Method and system for storing, organizing and processing data in a relational database

ABSTRACT

A system and method of processing a data query in a data processing system is provided. The data in the data processing system includes a plurality of individual data elements. The data elements are grouped and stored in at least one data unit. The information about the at least one data unit is gathered and stored in at least one information unit. The method comprises receiving the data query to be executed; using the information in the at least one information unit to optimize and execute the query; resolving the data query; and returning results of the data query for use by the data processing system.

CROSS REFERENCE TO RELATED APPLICATIONS

The present application is a Continuation of U.S. Nonprovisionalapplication Ser. No. 11/854,788 filed 13 Sep. 2007, which claims thebenefit of U.S. Provisional Application No. 60/845,167, filed 18 Sep.2006; each of which is incorporated herein by reference in its entirety.

TECHNICAL FIELD

The present disclosure relates generally to relational databasemanagement systems (RDBMS), and more particularly to a method and systemfor storing, organizing and/or processing data in RDBMS.

BACKGROUND

Databases and database management systems are being implemented in moreand more businesses, organizations and institutions, and are being usedto store and manage increasingly large amounts of data of increasinglydifferent types and complexity. As a result, there is a need forimproved database management solutions.

SUMMARY

A relational database management system (RDBMS) in which analyticalinformation about data and relationships within data are utilized inquery planning and execution is described along with methods for usingsuch an RDBMS. Analytical techniques such as rough set analysis (RSA)techniques may be applied to this analytical information to attempt tominimize the amount of information required to resolve a query and/ordetermine the fastest approach to retrieve the necessary data to answerthe query. In some instances, queries may be planned and executed basedon the analytical information (e.g., statistical information) about thedatabase without accessing the underlying data. Methods of compressionand decompression may also be applied to data stored in such RDBMS. Suchmethods may be optimized for the data.

In some aspects, there is provided a method of storing data. The datahas individual data elements. The method comprises grouping a pluralityof data elements of a same data type into at least one data unit;gathering information about the data elements of the at least one dataunit into at least one information unit; and storing the at least onedata unit and the at least one information unit on a storage device of acomputing device. Each of the data units may have at least onecorresponding information unit. A single information unit may beassociated with a single data unit. The information stored in the singleinformation unit corresponds to contents of the associated single dataunit. The overall size of the stored information units may not exceed 1%of the overall size of stored data units.

In some aspects, a single information unit may be associated with two ormore data units of the same data type. The information unit may groupthe data elements of the two or more data units. The information storedin the information unit may correspond to relationships between dataelements in the two or more data units.

In some aspects, the data elements may be arranged in a table havingcolumns and rows, where each data unit has a number of data elementsfrom a single column of the table. The data units having data elementscorresponding to the same rows of the table may be grouped into dataunit groups. Each data unit group may have an identification number andthe data elements correspond to the same row having the same positionwithin the data units of the data unit group.

In some aspects, a method of processing a data query in a dataprocessing system is provided. The data in the data processing systemincludes a plurality of individual data elements. The data elements aregrouped and stored in at least one data unit. Information about the atleast one data unit is gathered and stored in at least one informationunit. The method comprises receiving the data query to be executed;using the information in the at least one information unit to optimizeand execute the query; resolving the data query; and returning resultsof the data query for use by the data processing system.

In some aspects, the data elements are arranged in a table havingcolumns and rows with the data units having a number of data elementsfrom a single column of the table. The data units having data elementsfrom the same rows of the table may be grouped into data unit groups,each data unit group having an identification number and the dataelements correspond to the same row having the same position within thedata units of the data unit group.

Resolving the data query may include executing an execution plan. Theexecution plan may have a sequence of data processing operations.Execution of each of the data processing operations within the executionplan includes the steps of: using the information in the at least oneinformation unit to identify the data units containing the data elementsthat are to be retrieved to complete the operation; retrieving the dataelements in the respective data units needed to complete the operation;completing the operation; and if the completed operation is the lastoperation in resolving the data query, returning the results of the dataquery for use by the data processing system and if the completedoperation is not the last operation in resolving the data query,returning intermediate results of the operation for use by the remainingoperations in the execution plan.

In some aspects, further information units are dynamically created toreduce the time it takes to resolve a data query. The information in theat least one information unit may be dynamically altered to reduce thetime it takes to resolve a data query. The information in one or more ofthe at least one information units may be dynamically altered based onresults of one of the data processing operations to reduce the time ittakes to resolve a data query. Results of one of the data processingoperations may be used to determine parameters of subsequent dataprocessing operations to reduce the time it takes to resolve a dataquery.

In some aspects, a data processing system for storing data is provided.The data has individual data elements. The system comprises a serverhaving a processor for controlling operation of the server; a storagedevice coupled to the processor; and a memory coupled to the processor.The server is configured to: group a plurality of data elements of asame data type into at least one data unit; gather information about thedata elements of the at least one data unit into at least oneinformation unit; and store the at least one data unit and the at leastone information unit on the storage device. Each of the data units mayhave at least one corresponding information unit.

In some aspects, a data processing system for processing a data query isprovided. The data processing system has a server having: a processorfor controlling operation of the server; a storage device coupled to theprocessor; and a memory coupled to the processor. The system includes aplurality of individual data elements. The data elements are grouped andstored in at least one data unit on the storage device. Informationabout the at least one data unit is stored in at least one informationunit on the storage device. The server includes a query module residentin the memory for execution by the processor. The query module isconfigured to: receive the data query to be executed; use theinformation in the at least one information unit to optimize and executethe query; resolve the data query; and return results of the data queryfor use by the data processing system.

In some aspects, a computer program product having a computer readablemedium tangibly embodying code for storing data in a data processingsystem is provided. The data has individual data elements. The computerprogram product comprises: code for grouping a plurality of dataelements of a same data type into at least one data unit; code forgathering information about the data elements of the at least one dataunit into at least one information unit; and code for storing the atleast one data unit and the at least one information unit on the storagedevice.

In some aspects, a computer program product having a computer readablemedium tangibly embodying code for processing a data query in a dataprocessing system is provided. The system includes a plurality ofindividual data elements. The data elements are grouped and stored in atleast one data unit. Information about the at least one data unit isstored in at least one information unit. The computer program productcomprises: code for receiving the data query to be executed; code forusing the information in the at least one information unit to optimizeand execute the query; code for resolving the data query; and code forreturning results of the data query for use by the data processingsystem.

DESCRIPTION OF THE DRAWINGS

FIG. 1 is a schematic diagram of a relational database management system(RDBMS);

FIG. 2 is a schematic representation of a data pack consisting ofcompressed column-data stored in a database;

FIG. 3 is a flowchart illustrating operations of a method for generatingdata packs;

FIG. 4 is a schematic representation of a column being encoded duringthe operations of FIG. 3;

FIG. 5 is a flowchart illustrating operations of a method for decodingdata packs;

FIG. 6A is a schematic diagram illustrating an example of a HISTknowledge node;

FIG. 6B is a schematic diagram illustrating an example of a CMAPknowledge node;

FIG. 6C is a schematic diagram illustrating an example of a Graph Nodeknowledge node;

FIG. 6D is a schematic diagram illustrating an example of a Foreign Nodeknowledge node;

FIG. 7A is a diagrammatic representation of a method of compression;

FIG. 7B is a flowchart illustrating operations of a method forcompressing data;

FIG. 7C is a flowchart illustrating operations of a method fordecompressing data;

FIG. 8 is a schematic diagram illustrating operations of a method forcompressing data using a PartDict data filter;

FIG. 9 is a schematic diagram illustrating operations of a method fordecompressing data that was compressed using the PartDict data filter ofFIG. 8;

FIG. 10 is a schematic representation of data within the RDBMS of FIG.1;

FIG. 11 is a schematic representation of a tiered data warehouse;

FIG. 12 is a schematic representation of a tiered data warehouse system;and

FIG. 13 is a schematic diagram illustrating a computing device that maybe used to implement the methods disclosed.

Throughout the Figures, like features are identified by like referencenumerals.

DETAILED DESCRIPTION

The present disclosure provides a relational database management system(RDBMS) in which analytical information about data in a database may beused in query planning and execution. The analytical information may bedetermined from the data by mathematical techniques. Basic analyticalinformation about the data may be used to provide advanced analyticalinformation (i.e., higher level, more organized information) about thedata and relationships within the data. Analytical information usuallyapplied by conventional databases to single data values may be appliedat the level of collections of values stored in data packs as describedbelow.

Using basic and advanced information about the data, techniques such asrough set analysis (RSA) techniques may be used in query planning andexecution. RSA techniques are mathematic techniques based on rough settheory for providing statistical information about raw data. RSAtechniques apply mathematic analytical techniques to identifyrelationships between data and provide approximation data (e.g., roughsets) that represent these relationships. RSA techniques may also beused to generate and optimize the advanced information about the data tofurther improve query execution. Rough set theory can provide atheoretical basis for machine learning by which relationships may beidentified in raw data. Analytical techniques, such as various RSAtechniques, may be applied in the RDBMS.

The use of RSA techniques in query planning and execution seeks toimprove query response times and extend query capabilities compared withtraditional approaches to database management systems (DBMS). Usingbasic and advanced information about the data, RSA techniques allow theamount of data that needs to be accessed to resolve a database query tobe minimized by first analyzing the basic and/or advanced analyticalinformation to determine if this analytical information may be used,either entirely or in part, to resolve the query without accessing theunderlying data. The process of accessing data may require the data tobe retrieved from a storage medium, which may be costly in terms ofmemory or time. Further, when data needs to be accessed, the basicand/or advanced analytical information may assist in determining theoptimal approach to do it, as some data may need to be accessed multipletimes while resolving the given query. Further still, the basic and/oradvanced analytical information about the data may provide usefulinformation in managing or organizing the database, and may be used toextend standard functionality of the relational database managementsystem, such as optimization and execution of queries, by allowing“rough queries” based solely on the basic and/or advanced analyticalinformation about the data. These rough queries may be used as executionsteps in the resolution of a data query.

Relational Database Management System

FIG. 1 is a schematic diagram of a relational database management system(RDBMS) 200. The example RDBMS 200 is described with reference tofunctional program modules for the purpose of illustration only. Whenimplemented, one or more of these functional modules may be combinedinto a single program module or may include two or more sub-modules. TheRDBMS 200 may interface with a client application (also referred to as aclient) 202 providing a query tool executed on a user terminal (notshown). The RDBMS 200 may be coupled to a database 201, which may beimplemented using SQL (Structured Query Language), and may provide aninterface, such as an SQL interface, to query tools for use via theclient application 202. The SQL interface may manage the creation andmanagement of database objects like tables, views and user permissionsto those tables. Although only client application 202 is shown, multipleclient applications 202 may be connected to the RDBMS 200. The clientapplication 202 may provide a user interface (not shown) through whichSQL requests and responses may be sent and received between the clientapplication 202 and the RDBMS 200. The RDBMS 200 may include a QueryParser 204, a Query Representation Optimizer 206, a Query Optimizer 208and a Query Execution Module 210.

The RDBMS 200 may also include functional program modules such as:connectors, connection pool, management services and utilities, cachesand buffers, and file system. The functionality of these program moduleswill be understood by a person of ordinary skill in the art and so willonly be briefly described. The connectors may provide variousmechanisms/protocols for external tools to connect to the database. Theconnection pool may manage multiple clients connecting and makingrequests to the database. The connection pool may manage the concurrencyof many requests competing for resources. The management services andutilities are supporting tools that may be used with the data to manageadministration, settings, users, etc. The caches and buffers are lowerlevel services that may be provided to all the system components whichmanage data in memory (e.g., Random Access Memory (RAM)) for queryresponses and optimizations among other functions. The file system maymanage the storage device.

The database 201 may include one or more data packs (DP) 221 (see FIG.4). In some aspects, the database 201 may be a column-oriented database201, and the data packs 221 may each represent column data from a basetable. The data packs 221 may include compressed data representing therecords or entries in the respective column. The database 201 may bestored or partitioned on one or more data storage devices (not shown)such as a hard disk drive (HDD) or other suitable storage medium, whichmay be a permanent (i.e., non-volatile) storage medium. In some aspects,the data packs 221 may contain data concerning up to 65,536 consecutiverecords or records occupying up to 64 k of storage space, of which somevalues may be null. Depending on the size of a column, it may be storedin more than one data pack (e.g., if the column has more than 65,536records or requires more than 64 k of storage space). The data packs 221may contain more or less records than discussed here, depending on theapplication.

The data packs 221 may be compressed. In some aspects, data packs 221that are frequently accessed or frequently decompressed may also bestored in memory in compressed or uncompressed form in addition to beingstored in a storage medium, which may decrease access time or costs. Ifthe data pack 221 is stored in uncompressed form, memory usage may bedecreased by not having to decompress the data pack 221 to access itsdata elements. The frequency at which the data packs 221 in memory areaccessed may be monitored, and if the access frequency of a certain datapack 221 falls below a certain threshold, that data pack 221 may beremoved from memory. Similarly, the frequency at which data packs 221not stored in memory are accessed may be monitored, and if the accessfrequency of a certain data pack 221 is above a certain threshold, thatdata pack 221 may be copied and stored in memory in either compressed oruncompressed form. In other words, frequently accessed data packs 221may be cached in memory in order to reduce access delays.

Information about data elements in a data pack 221, such as basicstatistical or analytical information, may be stored in an informationunit or statistical data pack referred to as a data pack node (DPN) 222(see FIG. 4) associated with each data pack 221 in the database 201. Insome aspects, there may be one DPN 222 for each data pack 221. The datapack 221 and DPN 222 may be stored in the database 201 in separatefiles. Some collections of the data packs 221 may be stored in the samefiles. Each DPN 222 may contain basic information (e.g., statistics)about its respective data pack 221. The particular informationmaintained in the DPN 222 for each data pack 221 may depend on theparticular data type of the respective data pack 221. Typically, thesize of each DPN 222 may be small and so the DPN 222 may be notcompressed. However, if the information contained in the DPN 222 becomesmore complex or large, the DPN 222 may be compressed.

Generally, column data types may be at least one of: a string, a numericvalue, a floating point value, or a binary value. A compressionalgorithm may be selected for each of these four primary data types. Insome aspects, within each of these four primary data types there may besub-types (e.g., large string, short string, date, or other) for whichdifferent compression algorithms may be selected. In some aspects, allnumeric values, floating point values, and binary values may be storedas unsigned integers for the purpose of compression. For example, anegative decimal number may be converted to an unsigned integer, withcertain indicators to mark that it is a negative decimal number. Thiscan be reconstructed to recover the original number. By using onlyunsigned integers for storage, implementation of compression may besimplified by avoiding the need for different filters specific to alarge number of data types. Of course, certain data filters, for examplePartDict, may be able to process all data types. Data filters will bediscussed in greater detail further below.

In accordance with an embodiment, at least the following SQL-compatibledata types may be implemented (the particular format of each data typemay be varied): TINYINT, BOOL, BOOLEAN, SMALLINT, INT, FLOAT, REAL,DOUBLE, DEC, DATE, DATETIME, TIMESTAMP, TIME, YEAR, CHAR, VARCHAR,BINARY, VARBINARY, TINYTEXT, and TEXT.

In this example, the DPN 222 for data packs 221 may include: the numberof non-null values in the respective data pack 221, the minimum andmaximum values in the respective data pack 221, and the sum of values(this may be applicable to numerical data types only) in the respectivedata pack 221. In the case of non-numerical data types, thelexicographic minimum and maximum or the largest common prefix (e.g.,the longest sub-string that is common to all strings) for a data pack221 may be stored. If all non-null values in the data pack 221 are thesame, the DPN 222 may store only the statistical information andpositions of nulls. Additional or different information may be includedin the DPN 222 in other aspects. Further, other information about therespective data pack 221 may be derived from the information in the DPN222. For example, the average value may be derived directly from thenumber of non-null values and the sum of values which are both stored inthe DPN 222 of the example.

The DPN 222 may be used in query planning and execution, and may allowminimization of the need to access the data stored in the respectivedata pack 221 during query execution, as will be described in moredetail below.

For a data table with more than one column, DPNs with the same ordinalnumbers contain information about the same rows. For example, the valuesof the 100,000.sup.th row are stored in the second data pack for each ofthe columns because the first data packs may store the values for rowswith numbers between 1 and 65,536, and the second data packs for each ofthe columns store the values for rows with numbers between 65,537 and131,072. It is convenient to use the term “packrow” to indicate thecollection of data packs corresponding to different columns but storingthe values of the same rows. The packrows may also be referred to asdata pack groups or data unit groups. For example, the first packrowconsists of data packs storing the values of rows with numbers between 1and 65,536, the second packrow corresponds to the rows with numbersbetween 65,537 and 131,072, etc. A packrow may also be referred to as toits ordinal number plus references to particular data packs forparticular columns. A row with a given ordinal number (e.g., 100,000)may be trackable using the ordinal number of the packrow that it belongsto, for example number 2 for the 100,000.sup.th row, and the localordinal number of each of values of that row in each of thecorresponding data packs, which for this example is 34,464.

Knowledge nodes (KNs) or knowledge node objects 224 may be also storedin the database 201 on the same or different storage device (e.g., HDD)as the data packs 221 and the DPNs 222. The KNs 224 are another type ofinformation unit or statistical data pack, and may be directlyassociated with specific data packs 221. The KNs 224 may be associatedwith one or more data packs 221, one or more columns, one or moretables, or a combination thereof. KNs 224 may include information, suchas advanced analytical information, about the data elements stored inone or more data packs 221, one or more columns, one or more tables, ora combination thereof. The KNs 224 are optional in that they providemore detailed or advanced information about the data elements in thedata packs 221 and relationship between the data elements. There areseveral types of KNs 224 that may be generated and stored, and new KNs224 may be generated and added to the RDBMS 200 without impact orwithout significant impact to the basic functionality of the RDBMS 200.The KNs 224 may be dynamic and may change over time. KNs 224 provideinformation about data elements in data packs 221 that may extend beyondthat provided by the DPN 222, such as information about relationshipsbetween data elements in multiple data packs 221, information aboutrelationships between data elements in different columns in a base tableand/or relationships between data elements in columns in different basetables. Example KNs 224 are described below. Typically, the size of eachKN 224 may be small and so KNs 224 may be not compressed, however if theinformation contained in the KNs 224 becomes more complex or large theKNs 224 may be compressed. Together, the DPNs 222 and KNs 224 for thedatabase 201 form a knowledge grid (KG).

The database 201 may also have indices based on the data packs 221analogous to database indices based on records. Most standard databaseindices may be adapted for the RDBMS 200 to be based on the data packs221 and packrows rather than records.

The RDBMS 200 may further include a Knowledge Grid (KG) Manager 220 anda Knowledge Grid (KG) Optimizer 240. The Knowledge Grid Manager 220 is afunctional program module that may manage the DPNs 222 and KNs 224. TheKnowledge Grid Manager 220 works with the Query Optimizer 208 andKnowledge Grid Optimizer 240. The Knowledge Grid Manager 220 maygenerate, modify and remove KNs 224 based on instructions from theKnowledge Grid Optimizer 240. The Knowledge Grid Manager 220 may also beresponsible for informing the Query Optimizer 208 about the availableDPNs 222 and KNs 224 and may load them into memory from storage (e.g.,HDD) as necessary. When loaded into memory, the Query Optimizer 208 mayuse information from the DPNs 222 and KNs 224 to determine the optimumquery execution plan. Once loaded into memory the DPNs 222 and KNs 224may also be used in the execution of the query execution plan as will beexplained in more detail below.

The Knowledge Grid Optimizer 240 is a functional program module that maybe used to determine an optimum set of KNs 224 based on query statistics244 (which may be obtained from the Query Parser 204) and data packusage statistics 242 obtained from the Knowledge Grid Manager 220. Thequery statistics 244 may allow the Knowledge Grid Optimizer 240 toidentify data ranges and types of information about the data beingqueried. The query statistics 244 may be applied by the Knowledge GridOptimizer 240 to determine which KNs 224 are the most useful for queryexecution. For example, the query statistics 244 may maintaininformation on how frequently particular pairs of tables are joined,which may influence whether the corresponding pack-to-pack KN 224 shouldbe stored in the knowledge grid.

Data pack usage statistics 242 may store information about the frequencyand/or kind of usage (e.g., relevant, irrelevant, partially relevant) ofdata packs 221. Data pack usage statistics 242 may influencecompression/speed ratios for particular data packs 221 and which datapacks 221 are loaded directly into memory. Data pack usage statistics242 may also be used to vary the content of the DPNs 222 for particulardata packs 221. Data pack usage statistics 242 may be used to identifythe respective data packs 221 of the data being queried.

Using the query statistics 244 and the data pack usage statistics 242,the Knowledge Grid Optimizer 240 may update the optimal set of KNs 224.If KNs 224 do not exist over a desired data range, the Knowledge GridOptimizer 240 may determine that creation of one or more KNs 224 wouldimprove query execution. In this case, the Knowledge Grid Optimizer 240may instruct the Knowledge Grid Manager 220 to create one or more KNs224 of a given type (e.g., create a histogram for a given data pack221). If KNs 224 exist over a desired data range (e.g., for a given datapack 221) but, for example, the KNs 224 cannot be used to answer themost frequent queries over this range, the existing KNs 224 may bemodified (e.g., histogram ranges expanded) or new KNs 224 may be created(e.g., create a Pack-Pack Join object) in order to provide sufficientstatistics to satisfy such queries. In this case, the Knowledge GridOptimizer 240 may instruct the Knowledge Grid Manager 220 to modify orcreate the relevant KNs 224. If KNs 224 exist, but the relevant data isinfrequently or never queried, and hence the relevant KN 224 isinfrequently or never queried, these KNs 224 may be deleted. In thiscase, the Knowledge Grid Optimizer 240 may instruct the Knowledge GridManager 220 to delete the relevant KNs 224. If such data becomesaccessed or queried more frequently in the future, this may be detectedby the Knowledge Grid Optimizer 240, which may instruct the KnowledgeGrid Manager 220 to (re)create relevant KNs 224 if doing so will improvequery performance.

The Knowledge Grid Optimizer 240 provides an improved collection of KNs224, that is, KNs that cooperate with each other in an improved way inresolving queries. For example, two particular KNs may be far morevaluable than the rest of KNs. However, it may only be necessary toretain and store one of those two KNs because they usually aid inresolving the same queries with the same data packs. In such a case, itis possible to store only one of those two best KNs since they aresomewhat redundant. Instead of storing the second KN, it may bebeneficial to generate another more complementary KN that may be helpfulwith other queries or data packs.

Given the above example of how the task of optimizing a collection ofKNs may differ from the task of choosing KNs that appear to be the bestKNs, if these two tasks are viewed separately, some optimizationprinciples may be generated to be followed by Knowledge Grid Optimizer240. One objective of the Knowledge Grid Optimizer 240 is to maintain acollection of KNs 224 that are most useful in resolving queries. KNsthat are most useful in resolving queries are those KNs that protect orprevent the most data units from being accessed. Further, the KnowledgeGrid Optimizer 240 monitors the KNs 224 that are contributing leastwhile query optimization and execution occurs. The Knowledge GridOptimizer 240 searches for and generates new possible KNs to be storedinstead of those least contributing KNs. Some considerations forevaluating the contribution of KNs include overall disk space requiredfor storing all of the KNs 224 and total memory required to store theKNs 224 in memory.

The Knowledge Grid Optimizer 240 may use one or more heuristicalgorithms, including greedy, randomized, evolutionary and/or otherAl-based algorithms, to determine the optimal set of KNs 224 to satisfythe most queries with the least amount of access to the data in the datapacks 221, thereby minimizing the need to decompress the data packs 221and load the raw data into memory. Some suitable heuristic algorithmscan be found in “How to Solve It: Modem Heuristics” by Michalewicz andFogel; and in “Introduction to Algorithms” by Cormen, Leiserson, Rivest,and Stein. In one example, the Knowledge Grid Optimizer 240 may identifythe most significant relationships within the data and then maydetermine an optimal set of KNs 224 to be maintained by the RDBMS 200.The Knowledge Grid Optimizer 240 may then instruct the Knowledge GridManager 220 to generate, modify and/or remove KNs 224 in accordance withthe determined optimal set. Accordingly, the KNs 224 may be dynamic andchange over time in accordance with the query statistics 244 and thedata pack usage statistics 242. In addition, because the KNs 224 maycontain analytical information about data rather than actual data, theKNs 224 may be deleted or lost without compromising data integrity.

The Query Parser 204 may receive SQL queries, sometimes referred to asrequests or SQL statements, from the client application 202. The QueryParser 204 parses or separates the SQL query received from the clientapplication 202 and converts it into an internal representation usableby the RDBMS 200. The Query Parser 204 may forward this internalrepresentation to the Query Representation Optimizer 206.

The Query Representation Optimizer 206 may perform a syntacticaloptimization of the query. The Query Representation Optimizer 206replaces any references to views with references to the underlying basetables and reformats the query to attempt to make it easier to executebased on the structure of the query request. The Query RepresentationOptimizer 206 may forward this optimized representation to the QueryOptimizer 208.

The Query Optimizer 208 may optimize the query for execution usinganalytical information from the DPNs 222 and KNs 224 relating to theunderlying data (e.g., column, table, or view described in the originalSQL query) provided via the Knowledge Grid Manager 220, and fromintermediate query results obtained from the Query Execution Module 210as will be described in more detail below. The Query Optimizer 208 mayprepare the query for execution by preparing a query execution plan. Ifintermediate query results are obtained from the Query Execution Module210, the Query Optimizer 208 may modify the initial query execution planbased on these intermediate results. The Query Optimizer 208 may forwardthe initial and any modified query execution plans to the QueryExecution Module 210 for execution.

The Query Execution Module 210 may execute the initial and any modifiedquery execution plans provided by the Query Optimizer 208. The QueryExecution Module 210 may also be coupled to the Knowledge Grid Manager220 so as to provide access to the analytical information of the DPNs222 and KNs 224 for use in answering the query. The Query ExecutionModule 210 may, if the query cannot be resolved solely from the basicand/or advanced analytical information in the DPNs 222 and/or KNs 224respectively, use retrieved data from storage (e.g., HDD) bydecompressing the relevant data packs 221 in the database 201 using aDecompression Module 246. A file system (not shown) may be responsiblefor storing and retrieving data from data packs 221 in storage andcompressing/decompressing the data packs 221 as required.

The RDBMS 200 may further include import module 248 and export module250. The import module 248 may be used to encode raw data from basetables obtained from an external database or data source 203 into datapacks 221. The export module 250 may be used to decode data packs 221into underlying base tables for exporting to the data source 203. Aspart of the encoding/decoding operation, the data in the data packs 221may be compressed/decompressed. Compression may be performed by thecompression module 252 in the import module 248, and decompression maybe performed by the decompression module 254 in the export module 250.The compression and decompression may use the filter cascade describedbelow. The compression process may use a compression algorithm selectedto optimize the compression ratio of a column, and may be selectedaccording to the data type of the respective column. Possiblecompression algorithms encode the data and may use common coding methodsknown in the art including: arithmetic coding, range coding,Shannon-Fano-Elias coding, Shannon coding, Huffman coding, Rice coding,Golomb coding, Tunstall coding, and prediction by partial matching(PPM). Other codes or compression algorithms may be suitable. DPNinformation about the column may be used to vary parameters (forexample, to apply or not apply various parameters) of the selectedcompression algorithm. A complementary decompression algorithm may beused when the data packs 221 are decoded for export or query resolutionwhen data about individual data packs is required. The importingoperation may optionally include creation of KNs 224, using the KNCreation Module 256 in the import module 248. Compression anddecompression of the data will be discussed in greater detail below.

The database content and metadata may be stored in several types offiles: (1) column description files; (2) data pack description files;(3) data files; and (4) knowledge node description files.

The column description files contain a description of the column whichmay include: its data type, DPN information, and data pack localization.In some aspects, there are data pack description files in addition tothe column description files, in which case the column description filesmay contain information on the data type and basic statistics of thecolumn (e.g., information as to whether each record has a unique value,dictionary of values if a column has small number of distinct values, orothers), and the data pack description files may contain DPN informationand data pack localization information. The data pack localizationinformation associates each column with a respective data packidentifier (ID) such as a file name or number and file offset in caseswhere each data file contains more than one data pack. These files mayadditionally contain session information which may enable identificationof the sessions in which the given data pack was created or modified.The column description file may be a relatively small file, typicallyseveral kB in the present embodiment, which is loaded into memory whenthe respective column is used for the first time. The column descriptionfile is used to locate and decode data packs 221 by identifying theassociated data pack 221 and data type so as to identify the relevantdecompression algorithm. In some situations, the metadata which mayinclude the column description files from memory may be sufficient todetermine the content of the data pack (e.g. nulls only, all valuesidentical) in which cases the data file may be empty.

The data files contain the compressed column data. The data files may bestored with the column description file, although they may be stored ina different location (e.g., different storage device, etc.). In someaspects, the data files may be limited to 1.5 GB in size, or some othersuitable size depending on the application, to improve read times. Eachdata file may contain data for two or more data packs 221, where eachdata pack 221 in the data file occupies a continuous space. The numberof data packs 221 that may be stored in one file varies depending on thesize of each data pack 221, which may be influenced by data type, numberof nulls, and compression ratio. When reading data from a data pack 221,the file system may only need to decompress the part of the data fileassociated with the relevant data pack 221. Advantageously, storing morethan one data pack 221 in one data file may allow faster read times(e.g., times for decoding and loading of data pack data into memory foraccess during query execution) than if each data pack 221 were storedseparately. Additionally, storing larger collections of data packs 221together in files may improve the access speed. If each data pack 221were stored in a separate file, the access speed may be reduced.

The knowledge node description files store information about the KNs224. Each KN description file describes a separate KN 224, allowingindividual KNs 224 to be created, modified, or removed by the KnowledgeGrid Manager 220 without affecting other objects, such as other KNs 224.This may improve the ability to manage (e.g., by creation or deletion ofKNs 224) the KNs 224 of the database 201. The KN description files maybe stored on the same or different storage device (e.g., HDD) as thecolumn description files and data files. The KN description files areloaded into memory on the first use of the KNs 224, however theKnowledge Grid Manager 220 may load and remove KNs 224 from memory inaccordance with usage of such KNs 224. The KN description files are notnecessary for data decoding, and may therefore be lost or restored atany time.

All of the data files may be subject to memory management. Once thecolumn metadata (e.g., column description files), data pack (e.g., datafile) 221 or KN (e.g., KN description file) 224 is loaded into memory,it may be kept in memory as long as it is being used, and may be kept bythe memory manager for future use. When the available memory becomeslow, the less used objects may be removed from memory, as managed by theKnowledge Grid Manager 220.

Query Example

FIG. 2 illustrates an example query using data packs and analyticalinformation in the DPNs 222. FIG. 2 represents data packs includingcompressed column-data stored in the database 201. The following examplequery is to be solved using the database 201:

SELECT COUNT(*) FROM . . . WHERE A>5;

For the purposes of this example, the data packs in the database 201 canbe separated into three categories: BLACK data packs 260 are notrelevant to the query; GREY data packs 262 are partially relevant to thequery, so they need to be accessed to answer the query because some ofthe elements of the data packs 262 may be relevant and some otherelements may be irrelevant; and WHITE data packs 264 are relevant to thequery and do not need to be accessed to answer the query because theanswer can be determined directly from the respective DPN 222.

The BLACK data packs 260 each have a maximum value of less than 5 on A(e.g., some may have a maximum of 4, others 3, and yet others 2 or 1).These data packs are not relevant to answering the query because all ofthe data in these data packs 260 falls outside the query, because themaximum value of A of all records stored in the data packs 260 is 4which is less than the query restriction of A>5. The BLACK data packs260 are said to be disjoint with the query.

The WHITE data packs 264 each have a minimum value greater than 5 on A(e.g., some may have a maximum of 6, whereas others may be 7 or 10,etc.) on A. These data packs 264 are relevant to answering the querybecause the value of A for all records in these data packs 264 isgreater than or equal to 6 which is greater than the query restrictionof A>5. If the information in the DPNs 222 about the WHITE data packs264 indicates the number of records with non-null values on A, then thisinformation can be summed to partially resolve the query for the WHITEdata packs 264. The GREY data packs 262 each have a minimum value of 3and a maximum value of 7 on A. These data packs 262 are also relevant toanswering the query because some of the records in these data packs 262have a value which is greater than 5 on A, while some records in thesedata packs 262 have a value of less than 5 on A. Thus, only the WHITEand GREY data packs 264, 262 are relevant to answering the query. TheBLACK data packs 260 are not needed.

In the above example, information to answer the COUNT(*) query can beobtained from information regarding how many records from particulardata packs satisfy the filter A>5. Thus, for BLACK data packs 260 theanswer is automatically known to be zero. For WHITE data packs 264, weknow that all records with non-null values on A inside should becounted. The number of non-null values is stored in the DPN 222 for anumeric data type so this number can be taken and summed for all of theWHITE data packs 264. Only for the GREY data packs 262 is the number ofhow many of the non-null values that are actually greater than 5 notknown using information from the DPNs 222, and hence the GREY data packs262 need to be decompressed to get detailed information about everysingle value in the GREY data packs 262. The above example is providedfor the purpose of illustration only.

Data Pack Generation

FIGS. 3 and 4 illustrate operations 300 of a method for generating datapacks in accordance with an embodiment. The operations 300 illustratethe generation of a single data pack 221. If more than one data pack 221is needed, for example when a base table is being imported into theRDBMS 200, then the operations 300 must be repeated until the entirebase table has been encoded into data packs 221. Although the particularcompression algorithm may vary between data packs 221, the same steps302-320 will be performed for each column of the base table irrespectiveof the applied compression algorithm. FIG. 4 shows a partial column 402,its corresponding null mask 404, and a reduced data set 406 generated byremoving the null positions indicated in the null mask 404 from thepartial column 402.

In the first step 302, a column data from a base table to be encoded isselected, for example, when raw data from a base table is being importedinto the RDBMS 200. Next, in step 304 column data is obtained. In someaspects, each data pack 221 may hold records for up to 64 k (65,536) ofstorage space. Alternatively, each data pack 221 may hold 65,536records. The data pack 221 may hold more or less records than describedhere, depending on the application. If the column contains less then 64k of records, the column may be padded with null values to create atotal of 64 k column, or the column may be left with less than 64 k ofrecords, as an incomplete column. If the column contains more than 64 kof records, two or more data packs may need to be generated in order tocompress the entire column. In other aspects, the data packs 221 may beable to handle more than 64 k of records. Similar operations may becarried out where each data pack 221 is designed to hold 65,536 records.While a value of 64 k is used in this example for the size of eachcolumn, columns of any size may be used in order to meet therequirements of a particular application.

Next, in step 306 basic analytical information for the column data to bestored in the respective DPN 222 is calculated. Optionally, prior tocalculating the DPN information the column data may be analyzed foroutliers. If any outliers are detected, they form part of the DPNinformation stored in the DPNs 222. In addition, if any outliers aredetected they may be not considered in determination of the other DPNinformation (e.g. null, non-null, maximum and minimum where applicable,and sum where applicable). Outliers are values which do not match thegeneral pattern or trend in a given column. Outliers may be, forexample, infrequently occurring symbolic values or extreme values. Inthe example of FIG. 4, an outlier is the value 5000 which is an extremevalue compared to the other values in the column set (i.e., 6, 7 and 8).Outliers may be detected using heuristic methods including methodscommonly known in the art, which may be similar to those used in theKnowledge Grid Optimizer 240 described above (e.g., greedy, randomized,Al-based, evolutionary etc.). If detected, the outliers are stored inthe DPN 222 and an outlier mask 408 which tracks the positions ofoutliers to non-null values is stored in the data pack 221.

Next, in step 308 a suitable compression algorithm may be selected basedon the data type of the column. In some aspects, for each data typeimplemented in the RDBMS 200 a compression algorithm is pre-selected.Next, in step 310 a null (bit) map or null mask 404 (FIG. 4) isgenerated. Next, in step 312 the null mask is compressed using acompression algorithm suitable for binary sequences since the null mask404 is binary regardless of data type of the reference column.

Next, in step 314 the null mask 404 is used to remove null values fromthe column to generate a reduced data set 406 consisting of onlynon-null values. The null mask 404 provides a map of the null valuepositions and non-null value positions so that null value positions maybe removed from the column. As will be described in more detail below,the null mask 404 is stored in the data pack 221 for subsequent use indecompression.

Next, in step 316 the DPN information is used to determine parameters ofthe selected compression algorithm based on value patterns identified inthe reduced data set 406. The Knowledge Grid Manager 220 also providesinput regarding whether the data in a given data pack 221 may be morehighly compressed, for example because it is not used frequently, whichmay permit a high compression ratio to be utilized at the expense ofdecompression speed, or when decompression speed should be favoured atthe expense of compression ratio because the data in a data pack 221 isused frequently. The DPN information may be used to tune the parametersand optimize the compression ratio within every single data pack 221based on intrinsic patterns in the reduced data set 406. Next, in step318, the reduced data set 406 column data is compressed using theselected compression algorithm with the compression parametersdetermined in step 316, thus creating a DP 221. Next, in step 320compressed (non-null) column data, the compressed null mask 410, outliermask 408 (if any), compression parameters, and any intermediate resultsof the compression algorithm (if any) are stored together in the datapack 221, and a DPN 222 is stored in non-volatile memory (e.g., HDD).

The steps 302 to 320 are then repeated for the next column, or nextportion of the column, until all data in the base table to be encodedand stored has been processed via the operations 300.

FIG. 5 illustrates operations 500 of a method for decoding data packs221 in accordance with an embodiment. Decoding operations 500 may occur,for example, during execution of a database query in which access todata in the data packs 221 is needed or during export operations inwhich raw data from the RDBMS 200 is exported to the externaldatabase/data source 203. In the first step 502, an appropriatedecompression algorithm is selected for the data pack 221 to be decodedbased on its data type as determined from the column description fileassociated with the data pack 221 and maintained by the RDBMS 200.Compression parameters and any intermediate results of the compressionalgorithm (if any) which are stored in the data pack 221 are used bydecompression algorithm to decompress the data pack 221.

Next, in step 504 the compressed data in the data pack 221 isdecompressed to produce a reduced data set 406 (FIG. 4). Next, in step506 the null information stored in the associated DPN 222 (i.e., thenull mask 404) is applied to the reduced data set in order to producethe original column data.

Although the above description refers to compression and decompressioninvolving filtering of the outliers and null values, other filteringmethods are possible, and may be selected depending on the data. Theseother methods will be discussed in greater detail.

Multi-Column Encoding

In addition to encoding a single column, data packs 221 may be encodedbased on functional dependencies between columns within a base table oracross base tables. To accommodate multi-column compression,descriptions of these functional dependencies may be stored instead ofthe actual data for each column. For example, if it is determined thatwhenever column A has value x, column B has value y, then the value of ydoes not need to be explicitly stored. The value x in column A may beencoded into data packs in accordance as described above along with adescription of the functional dependency between columns A and B (i.e.the relation x=y). This relation may occur within the context of asingle base table or between columns in different base tables.

At the knowledge node level, if a multi-column dependency is known, itcan be used to minimize the number of data packs 221 to be decompressed.For example, if the values of column A are always greater than those ofcolumn B, during the execution of a query with Filter A=5, theninternally the additional Filter B<5 may be generated by the QueryOptimizer 208 to be used against the DPN information for the data packs221 for the column B.

Methods that may be common in the art (e.g., neural networks, fuzzylogic, rough set reducts, statistical regression, etc.) may be used toidentify functional dependencies between data. Alternatively, functionaldependencies may be input from existing database indices when data isimported into the database 201. Optimization criteria for identifyingfunctional dependencies may also be modified to search for inexactfunctional dependencies, such as where there are exceptions to therelationship between the data or a trend/pattern in the data (e.g.,outliers as described above). In the case of inexact functionaldependencies, the description of the relationship and any exceptions orspecial cases may be stored in the data pack 221 along with the data ofat least a reference column in accordance with the operations 300described above. For example, if x=y for columns A and B respectively,except for 2 records, the exceptional records (e.g., outliers) may bestored along with the relation x=y in the respective data pack 221. Suchfunctional dependencies may be smaller in size (e.g., require lessmemory) than the underlying data, and may be stored in KNs 224. Thus,KNs 224 may be created before data is grouped into data packs 221.

Thus, a mathematical dependency model may be generated based on one ormore functional dependencies describing an inexact functionalrelationship between data in columns A and B that allows, with the inputof values of some subset of data in column A, the output ofapproximations of the actual values of the corresponding subset of datain column B. This dependency model may then be modified to optimize thecompression ratio of data of column A to be compressed during encodingoperations using the particular compression algorithm applied for aparticular data type, provided that the resulting approximation of theactual data of column B is within the specified tolerance for the givenapplication. The above examples are for illustration purposes only.

Knowledge Nodes (KNs)

Some KNs 224 will now be described. Other KNs 224 are also possible. TheKNs 224 and their examples described below are provided for the purposeof illustration only. In general, KNs 224 can provide statistical andrelational data between columns in the database 201 or among data packs221 in a single column.

Value-Pack Histogram (HIST)

The scope of the HIST object or HIST knowledge node may be a singlenumerical column. The HIST object contains binary information about theexistence of a given value, or any value from a given interval, in therespective data pack 221. The HIST object is used, for example, torefine condition checking (e.g., exclusion of a data pack 221 when it isknown that a specific value is not present within that data pack 221)and joining (e.g., exclusion of pairs of data packs 221 when theirhistograms are disjoint).

The HIST object stores binary information about whether the data pack221 has any data elements with values in a given interval. The defaultnumber of intervals may be 1024, excluding minimum and maximum values,or it may be some other number. The intervals may be fixed-sizedintervals, depending on minimum and maximum values in a pack.Alternately, the intervals may be of variable size, such as with smallerintervals where the values have a higher distribution and largerintervals where the distribution is lower, for example where the valueshave a bi-modal distribution. The interval sizes may also be variabledepending on what range of values have a higher frequency of queries. Ifthe difference between the minimum and maximum values is less than thedefault or selected number of intervals, and the column is fixed-size(e.g., integer or decimal), then exact data values may be used as theintervals.

The HIST object may implement a function called IsValue (v1, v2) wherev1 and v2 are two numerical values defining an interval. The result ofthe function may be one of three values: RS_NONE if none of the entriesin a data pack have a value within the interval defined by v1 and v2;RS_SOME if some of the entries in a data pack have a value within theinterval defined by v1 and v2; and RS_ALL if all of the entries in adata pack have a value within the interval defined by v1 and v2.

One example of this knowledge node is illustrated in FIG. 6A. In thisexample, data elements 602 have a minimum value of 1 and a maximum valueof 9. Since the difference between the minimum and maximum values isless than the default resolution of 1024, the exact values are stored.The HIST object 604 uses binary values to indicate the presence ofcertain values (i.e., 1, 2, 3, 5, 6 and 9) with a 1 bit. In thisexample, IsValue(1, 4) would return RS_SOME, IsValue(7, 8) would returnRS_NONE, and IsValue(1, 9) would return RS_ALL.

Pack-Pack Join (JPP)

The scope of the JPP object may be any two columns from different basetables. The JPP object contains binary information about whether a givenpair of data packs 221 containing column data from different base tableshas any common value, except nulls. The JPP object may be used, forexample, as an additional criterion while joining to exclude pairs ofdata packs 221 which are not joinable.

The JPP object may implement a function called GetValue (p1, p2) wherep1 and p2 refer to two data packs 221. The result of the function may bea Boolean: True where data packs p1 and p2 may have non-emptyintersection, and False where data packs p1 and p2 have no commonvalues.

Character Map (CMAP)

The scope of the CMAP may be a single text column. The CMAP object is abinary map indicating existence of any character at any position. Forexample, for every position n (which may be limited to up to 64characters) a binary string of 256 bits (32 bytes) is prepared. The sizeof the binary string may be different, depending on the application. Avalue ‘1’ on character i means that there is at least one record in thedata pack 221 for which there exists character i on position n.Characters at positions starting from 65 may be ignored, although thenumber of positions considered may be greater or smaller, or there maybe no limit. The CMAP object may be larger than the HIST object fornumerical values, but may be used for many optimizations, serving asstatistics for sorter definitions and providing quick answers for somequeries with ‘LIKE’ and other text predicates.

The CMAP object may implement a function called IsValue (v1, v2) wherev1 and v2 are two string values defining an interval. The result of thefunction may be one of three values: RS_NONE if none of the entries in adata pack have a value within the interval defined by v1 and v2; RS_SOMEif some of the entries in a data pack 221 have a value within theinterval defined by v1 and v2; and RS_ALL if all of the entries in adata pack 221 have a value within the interval defined by v1 and v2.

The CMAP object may also implement a function called IsLike (string)where string is a string instance that CMAP looks for in the column. Theresult of the function may be one of three values: RS_NONE if none ofthe entries in a data pack 221 matches the pattern; RS_SOME if some ofthe entries in a data pack 221 match the pattern; and RS_ALL if all ofthe entries in a data pack 221 match the pattern.

The CMAP object may also implement functions called GetMin (pack) andGetMax (pack) where pack is a data pack 221 in the database 201. Theresult of these functions may be a string composed of the minimum ormaximum characters of the histogram at every position in the data pack221 for the GetMin (pack) and GetMax (pack) functions respectively.

One example of this knowledge node is illustrated in FIG. 6B, where dataelements 606 result in CMAP object 608. In this example, CMAP object 608may be case-insensitive, meaning that CMAP object 608 will indicate thepresence of a certain character with a 1 bit regardless of whether it isuppercase or lowercase. Hence, the element “Banana” results in a 1 bitindicator for “B” at position 1 and for “A” at positions 2 and 4. Inother implementations, CMAP object 608 may be case sensitive, mayinclude special symbols, and/or may include numerical data. While theCMAP object 608 is only shown with letters A-I and positions 1-4, anycombination of alphanumeric characters, typically all letters of thealphabet, may be included, and any number of character positions may beincluded.

Graph Node

The scope of a Graph Node may be two or more columns within a singletable. Graph Node knowledge nodes may represent information aboutdependencies between the data elements of the columns. Graph Nodes mayhave the structure of a graph having multiple nodes connected by paths,each path being labelled by a query-like condition. Thus, as the graphis traversed along the paths, the query-like condition of each path isadded, such that a certain node in the graph contains information thatsatisfies all the conditions in the paths leading up to that node.

While the Graph Node associated with one column may contain informationon the data elements of that one column in the table, the paths may belabelled with conditions related to the other columns in the table. TheGraph Node may contain a base node that provides information on theassociated column without any additional condition.

One example of this knowledge node is illustrated in FIG. 6C. In thisexample, the Graph Node includes nodes containing information about thedata elements in column A in a table. In this example, column A containsalphanumeric data elements, so the information about column A resemblesa CMAP object. However, the Graph Node also contains CMAP objectscalculated under additional conditions specified over columns B and C,belonging to the same table. The node 610 contains a CMAP of column Awithout satisfying any other condition. The presence of bit ‘1’ at row1, column 2 of node 610 indicates that column A contains a data elementhaving a string that has character ‘A’ at the second position.Traversing to node 612 applies the condition C=‘dom’, resulting in aCMAP of data elements in column A that share a row with a data elementin column C that contains the string ‘dom’. Node 612 has a bit ‘0’ atrow 1, column 2, indicating that there is no data element in column Awith a string having ‘a’ at position 2 that satisfies the conditionC=′dom′. A similar node 614 can be constructed for the condition B>7.Nodes 612 and 614 can be further combined in node 616, which containsinformation satisfying both C=′dom′ and B>7. Node 618 shows that similaroperations can be carried out for other query-like conditions.

Foreign Node

The scope of a Foreign Node may be two or more columns in two or moretables. A Foreign Node may contain information similar to that stored inDPNs and KNs, but rather than being derived from the data elements ofthe table to which is it associated, a Foreign Node may containinformation based on data elements of a different table. The ForeignNode for a first data pack in a first table may have information basedon one or more second data packs in a second table, the second datapacks and the first data pack having a relationship of interest, whichmay be a query-like condition. The relationship of interest may bebetween rows of data elements in the data packs rather than the datapacks in their entirety, but the Foreign Node may still be associatedwith the data packs. In this way, the Foreign Node allows not only theidentification of data packs that contain data elements satisfying acertain column-to-column or table-to-table relationship, but alsoprovides information on the data elements satisfying that relationship.

One example of this knowledge node is illustrated in FIG. 6D. In thisexample, a simple database contains Table 1 and Table 2, each havingfour rows of numeric data elements (numbered 1 to 4), with data packseach containing two rows of data elements. Table 1 contains columns Aand B while Table 2 contains column C. Consider the following query:

T1.A=1 AND T1.B=T2.C

That is, the query seeks to find all instances in column A where thedata element has the value 1 and where the value in the correspondingrow in column B has an equal value in column C. As shown by the lineslinking elements in column B to elements in column C, there are severalinstances where T1.B=T2.C, and this may be indicated by other knowledgenodes, such as the JPP object described above. However, only row 1 has adata element in column A equal to 1 where the value in column B has anequal value in column C, as required by the query. A JPP object mayindicate rows 1, 2 and 4 without providing specific information on thedata elements, and direct access to the data elements of all three rowsmay be required to resolve the above query.

Foreign Nodes may allow information about column A to be stored in a KNassociated with column C. In this example, for the top data pack 620 incolumn C, there are two equal values in column B, namely in rows 2 and4. The corresponding values in column A are 2 and 0, respectively. Thisinformation is used to create Foreign Node 624 for data pack 620.Foreign Node 624 contains the minimum and maximum values, and ahistogram for column A for data elements belonging to a row thatsatisfies the relationship T1.B=T2.C. The Foreign Node 624 indicatesthat the minimum value is 0, the maximum value is 2, and the presence ofthe values 0 and 2 is indicated with a ‘1’ bit, the absence of the value1 is indicated with a ‘0’ bit. Similarly, for the bottom data pack 622in column C, the data elements of interest in column A contain thevalues 0, 1, and 2. The Foreign Node 626 for data pack 622 indicatesthat the minimum value is 0, the maximum value is 2, and the presence ofthe values 0, 1 and 2 is indicated with a ‘1’ bit.

Thus, by considering the Foreign Nodes of Table 2, it can be determinedthat the top data pack 620 does not satisfy the query since there is nocorresponding data element in A with the value 1. Hence, only the dataelements of the bottom data pack 622 need to be considered, thusdecreasing the required amount of direct access to the data elements.

Examples of Use of Knowledge Nodes

Examples illustrating the use of KNs 224 will now be described. Theseexamples are provided for the purpose of illustration only. Assume thereare two base tables, table T and table X. Table T includes columns A, Band C (there may be more in table T, but they are not relevant to theexample). Columns A and C contain numeric data. Column B contains stringdata. Each of columns A, B and C in table T is represented by 5 datapacks for every column as illustrated below:

TABLE T Column A Column B Column C (numeric) (string) (numeric) Datapack 1 Data pack 1 Data pack 1 Data pack 2 Data pack 2 Data pack 2 Datapack 3 Data pack 3 Data pack 3 Data pack 4 Data pack 4 Data pack 4 Datapack 5 Data pack 5 Data pack 5

Table X includes columns D and E. Columns D and E contain numeric data.Each of columns D and E in table X are represented by 3 data packs forevery column as illustrated below:

TABLE X Column D Column E (numeric) (numeric) Data pack 1 Data pack 1Data pack 2 Data pack 2 Data pack 3 Data pack 3

Character Map (CMAP)

Consider the following query:

SELECT MAX (A) FROM T WHERE B=‘good’;

Assume that the data pack 1 for column A has a MAX=5, data pack 2 forcolumn A has a MAX=2, data pack 3 for column A has a MAX=8, data pack 4for column A has a MAX=5, and data pack 5 for column A has a MAX=10. Forevery data pack for column B, the function IsLike (‘good’) is executed.Assume the following results: data pack 1 for column B has aresult=RS_ALL, data pack 2 for column B has a result=RS_SOME, data pack3 for column B has a result=RS_NONE, data pack 4 for column B has aresult=RS_NONE, and data pack 5 for column B has a result=RS_SOME.

Combining the above information about A and B indicates the following:

Data pack 1 for column A has MAX=5, so no decompression is neededbecause all data relevant (all data records in data pack 1 for columnmatch).

Data pack 2 for column A has a MAX=2, so these records are ignoredirrespective of the data in column B since we already know that MAX(A)will be at least 5 because of results from the data pack 1 on A. Henceno decompression is needed.

Data packs 3 and 4 for column A are not relevant, because these wereexcluded by the filter on column B since there was no match on column B.

Data pack 5 for column A and B requires decompression because the numberof relevant values (all/none) cannot be determined. For A some recordshave A>5, and for B some of the records match, but it cannot be saidwhether there is a match where A>5, and if so the MAX (A) cannot bedetermined.

Thus, the final result of the query will be the maximum of 5 (from datapack 1 on column A) and the result of the analysis of data pack 5 (i.e.,the maximum value subject to the filter B=′good′ for single records inthe data pack).

Pack-Pack Join (PPJ) and Value-Pack Histogram (HIST)

Consider the following query:

SELECT MAX (T.A) FROM T JOIN X ON T.C=X.D WHERE T.B=‘good’ AND X.E<5;

The filter B=′good′ may be applied in table T in the same manner as inthe previous example. Hence, only data packs 1, 2 and 5 from table T aregoing to participate in the calculations. Now for every data pack forcolumn E in table X we apply the HIST object's function Is Value (5, n)where n equals the maximum value for column E in table X (which isdetermined from column E's DPNs 222). Assume the following results:

Data pack 1 for column E has a result of RS_SOME,

Data pack 2 for column E has a result of RS_ALL, and

Data pack 3 for column E has a result of RS_NONE.

Therefore, data pack 3 for column E can be ignored in furthercalculations as none of the records are relevant. Going further to theJOIN operation, we know that the data packs to be involved are data pack1, 2 and 5 for column C in table T, as well as data packs 1 and 2 forcolumn D in table X. Consider that JPP for T.C and X.D is given and itresults as follows for function GetValue (C's data pack from table T,D's data pack from table X):

Data pack 1 on column C and data pack 1 on D is false,

Data pack 1 on column C and data pack 2 on D is false,

Data pack 2 on column C and data pack 1 on D is true,

Data pack 2 on column C and data pack 2 on D is false,

Data pack 5 on column C and data pack 1 on D is true, and

Data pack 5 on column C and data pack 2 on D is false.

Thus, the results narrow down the column C's data packs which are goingto participate in the calculation of MAX(T.A) to data packs 2 and 5because the elements of data pack 1 are not going to occur in a joinedtable (neither with elements of data packs 1 nor 2 for D). Hence, datapacks 2 and 5 for column A are decompressed and the maximum iscalculated subject to the filter B=′good′ in table T.

Compression Algorithms

The compression of column data within data packs 221, as opposed tocompressing entire columns, may yield higher compression ratios than ifan entire column where compressed because of the ability to identifyrelationships or redundancies within each data pack 221 that may notexist over the entire column, thereby providing a basis for a potentialhigher compression ratio within the data pack 221.

To compress a sequence of data elements, relationships or redundanciesare found between the elements. There may be many possible relations,applicable to numeric values, alphanumeric values, or both, non-limitingexamples of which are as follows:

1. All numbers fall into the same range [min, max];

2. Some values may appear frequently in the sequence (frequent values);

3. A group of bits (usually the highest, sometimes the lowest) may besimilar in all elements of the sequence or may have highly non-uniformdistribution;

4. A group of bits at a given position in each element may have highlynon-uniform distribution;

5. Repetitions may occur frequently;

6. Neighbouring elements may be correlated;

7. All elements may be divisible by the same value>=2; and

8. A few elements may take very different values than the rest.

Filter Cascade

These relations/redundancies are utilized in filtering and compressingthe data using a series of compression filter stages in a filtercascade, as illustrated in FIG. 7A, in which the compression filterstages 703 a-n are arranged, for example, with the output of onecompression filter stage (e.g., 703 a) providing the input to asubsequent compression filter stage (e.g., 703 b). The filter cascademay be provided with an uncompressed data input 701. Each compressionfilter stage 703 a-n applies a data filter 702 a-n and a compressionblock 704 a-n. Each data filter 702 a-n in the series may be designed todetect and filter out a different type of relationship/redundancy in thedata. At each data filter 702 a-n, a description of therelationship/redundancy may be sent as reconstruction data to be encodedby the compression block 704 a-n using a standard compression algorithmand filtered from the data. The compression algorithm may be differentfor each compression filter stage 703 a-n, and may have differentparameters, depending on the data filter 702 a-n and/or the input data.Commonly used compression algorithms apply arithmetic or range coding.Other suitable codes include Shannon-Fano-Elias code, Shannon code,Huffinan code, Rice code, Golomb code, Tunstall code, and prediction bypartial matching (PPM). The output of the compression block 704 a-n isthen sent to the filter stream 708. The filter stream 708 may includethe output of each compression block 704 a-n appended together. Thefiltered data of compression filter stage 703 a forms the filter output706 a of that compression filter stage 703 a and may be sent as theinput of the next compression filter stage 703 b. This may be repeatedfor each compression filter stage 703 a-n in the series.

In some aspects, at the end of the filter cascade, the final compressionfilter stage 703 n sends all of its data input to the compression block703 n, so that it has no filter output. Thus, at the end of the filtercascade, all the data may be compressed and represented in the filterstream 708. Typically, the last compression filter stage 703 n in thefilter cascade may assume its input data has uniform distribution andmay compress all of its input data. The filter stream 708 may have anuncompressed information header at the start of the compressed data,which stores information (e.g., in the form of binary flags) indicatingwhich compression filter stages 703 a-n were applied during compression.In some aspects, this information header may be stored in the columndescription file described above.

In some aspects, during decompression, two passes through the filtercascade may be used. The first pass is from the beginning to the end, inthe same order as during compression, to load the encoded descriptionsof each compression filter stage 703 a-n from the filter stream 708. Thesecond pass is in the reverse order to reconstruct the original datafrom the filter stream 708. Each data filter 702 a-n may have its ownreconstruction and/or decompression algorithm. Alternatively, theinformation header may be designed to eliminate the need for the firstpass of the two passes described above.

Reference is next made to FIG. 7B which illustrates processing of databy the filter cascade for compression, and FIG. 7C which illustratesprocessing of data by the filter cascade for decompression.

For compression (FIG. 7B), at step 710 the data may be introduced to thefilter cascade.

The next compression filter stage in the cascade (i.e., the firstcompression filter stage in the case where the data is first introduced)may be considered for effectiveness at a step 712. In considering thecompression filter stage, it may be evaluated to determine whether usingthe data filter of that filter stage improves the compression of thedata. Each filter stage may have its own method of evaluation thatevaluates or predicts the effectiveness of applying the data filter.This will be discussed in greater detail further below, with referenceto some data filters. At a step 714 if the data filter is found toprovide acceptable or improved compression, then the compression filterstage may be selected to be used and the process proceeds to a step 716.If not, the compression filter stage may be not selected for use and theprocess may return to step 712. What constitutes acceptable compressionresulting in use of a particular filter stage may be fully configurabledepending on the design criteria of a particular application.

At the step 716, the data filter is applied to the data. Reconstructioninformation, which may include the relationship/redundancies filteredout from the data, may be sent to the compression block. While the step716 may be only arrived at if the data filter evaluated at the step 714is considered to provide a certain level of compression, in some aspectssteps 716-720 will always be executed for the last compression filterstage, as described above with reference to FIG. 7A.

At a step 718, the reconstruction information is compressed by thecompression block of the filter stage, and the compressed reconstructioninformation may be provided to the filter stream. The remaining filtereddata may be provided as the filter output. The reconstructioninformation may include information on how to reconstruct the input datafrom the filtered data.

At a step 720, if there is a subsequent compression filter stage to beevaluated or considered for use in the filter cascade, then the filteroutput may be sent as the input data for the next compression filterstage and the process returns to step 710. If there are no more filters,the process ends. At the end of the filter cascade, all the originaldata may be compressed in the filter stream and the filter output may beempty.

If there is a subsequent filter stage, but the filter output is alreadyempty, the subsequent filter stage may be not used and the process mayend. This may occur where the data is redundant enough that removal ofcertain redundancies filters all the data for inclusion in the filterstream, for example, in data where all elements have a high frequency ofoccurrence. Indication that the subsequent filter stage was not used maybe added to the filter stream or to the header data, so that the datacan be properly reconstructed during decompression.

FIG. 7C illustrates an operation for decompression of compressed data.At a step 740, the filter input may be initialized to empty.

Next, at a step 742, the filter input may be provided to the filtercascade. At the start of the decompression process, the filter input maybe typically empty. In some aspects, the filter input may be initializedto contain some data, for example where some of the data was alreadydecompressed or was not compressed. Next, at a step 744, the filtercascade is applied in the reverse order to the order described above forcompression. In some aspects, the order in which compression took placemay be pre-set and known. If the compression order is not known, thisinformation may be provided in an uncompressed header block associatedwith the compressed data. The compression order may also be provided inan information header (e.g. a column description file) associated withthe compressed data file.

In some aspects, there may be a preliminary step (not shown) in whichthe compressed data may be processed by the filter cascade in the sameorder as during compression. This preliminary step allows extraction ofdata reconstruction information, such as identification of which filterstages were applied, for each filter stage from the filter stream. Thispreliminary step may also partition the filter stream into blocks ofcompressed data generated by separate compression filter stages.

Next, at a step 746, the filter stream may be read by the filter stageand the reconstruction information may be extracted. The reconstructioninformation may be decompressed by applying a corresponding decodingalgorithm according to the compression algorithm used. Thereconstruction information may be relevant only to the particular filterstage being considered, or it may contain information for other filterstages in the cascade, in which case only the relevant information maybe considered.

Next, at a step 748, the reconstruction information is used toreconstruct the data from the filter stream. The method ofreconstruction may be unique to each filter stage. If the filter streamhas passed through the entire filter cascade in reverse as appropriate,the data may now be fully reconstructed. Otherwise, the data may be onlypartially reconstructed. The fully or partially reconstructed data maybe written to the output of the filter cascade.

Next, at a step 750, if there is another filter stage in the reversecascade, the process proceeds to a step 752 where the output (e.g., thepartially reconstructed data) of the previous filter stage may beprovided as the filter input for the next filter stage. The process thenreturns to the step 742 to move to the next filter stage. If there areno more filter stages in the reverse cascade, the output may now consistof the fully reconstructed data and the process ends. In the case wherethe compressed data is fully recoverable, the output consists of fullyreconstructed data that may be identical to the original uncompresseddata.

The filter cascade may be flexible in that it may separate differenttypes of redundancies which simplifies filter design and implementation,may allow easy switching on/off of some of the filter stages, and mayallow the same type of filter stage to be used several times in theseries. In some aspects, the order in which the filter stages areapplied may be varied, and may be dynamically varied. Some examples ofthe types of the data filters that may be used are described below.These examples are for the purpose of illustration only. Some datafilters may be used with numeric data only, alphanumeric data only, orboth:

1. Min: subtracts the minimum of all data elements from each element ofthe data, thus reducing the range of the data; the value of the minimumis compressed and sent to the filter stream. To reconstruct the data,the minimum value is decompressed and added onto each element.

In evaluating whether to use this filter, typically the Min filter maybe applied if it reduces the range of the data elements. In general,this means that if the data elements have a non-zero, preferablypositive minimum value, the Min filter may be applied.

2. GCD: determines the Greatest Common Divisor (GCD) of all dataelements, and divides each element by the GCD; the value of the GCD iscompressed and sent to the filter stream. To reconstruct the data, eachelement is multiplied by the GCD.

Typically, the GCD filter may be applied if a GCD exists. In general,this means that if the data elements have a GCD greater than 1, the GCDfilter may be applied.

3. Diff: calculates a differenced sequence consisting of differencesbetween pairs of subsequent elements (i.e., between element n andelement n+1), and sends the differenced sequence to the next stage ofcompression. In some aspects, the first element in the sequence isunchanged. Values of differences may be taken modulo (max_value+1),where max_value is the maximum value of all data elements. The result isthat any difference that is less than zero will have max_value added toit, so they fall into the same range as the original values, while datareconstruction is still possible. The max_value is compressed and sentto the filter stream.

Reconstruction of data filtered through the Diff filter may be done asfollows: the first element is unchanged, so it is taken as-is; thesecond element is reconstructed by adding the second element of thedifference sequence to the first element; the third element isreconstructed by adding the third element of the difference sequence tothe reconstructed second element, and so on through the entiredifference sequence. If modulo was applied to the difference sequence,this also should be reversed in order to recover the data exactly. To dothis, the max_value is decompressed from the filter stream. Then anyreconstructed element that has a value greater than max_value will havemax_value subtracted from that element.

Determination of whether the Diff filter may be applied may be typicallybased on calculations of the entropy of the data with and withoutapplying the filter. The differences of the data are calculated and theentropy of the differenced data is compared to the entropy of theoriginal data. If the entropy of the differenced data is smaller, thenthe Diff filter may be applied. In order to speed up calculations, insome aspects, this determination may be based only on a sample of thedata. For example, only 5% of the data may be differenced and used forentropy comparison. In some aspects, the entropy may be calculated usingonly certain bits of the data, for example the top 8 and bottom 8 bitsseparately. When only 8 bits are considered, only 256 different valuesmay occur, which results in entropy calculations that are easier andmore reliable. If all values in the data are shorter than 16 bits, thenumber of bottom bits considered may be appropriately decreased. If allvalues in the data are shorter than 8 bits, only the 8 top bits may beused to calculate entropy.

4. PartDict: builds a partial dictionary of values occurring in thesequence and uses it to compress the data with a standard encodingalgorithm, such as range coding (RC). The frequency of each value in thedictionary may be also stored to calculate probabilities forcompression. The dictionary is referred to as partial because onlyfrequent values are stored, meaning values having a number ofoccurrences above a certain constant threshold, which may bepre-determined. If a rare or non-frequent value occurs in the sequenceduring encoding, a special escape symbol is encoded and the rare valueis left in the sequence, forming the filter output for input to the nextfilter in the filter cascade. Frequent values are removed from the dataafter encoding. Before encoding of the data, the dictionary iscompressed sent to the filter stream. The encoded sequence of frequentvalues is also sent to the filter stream.

Typically, determination of whether to apply the PartDict filter may bebased on a comparison of the compressed data size after applying boththe PartDict filter and the Uniform filter (discussed below) and thecompressed data size after applying the Uniform filter alone. It may bepossible to predict the compressed data sizes for both cases withoutactually carrying out the compression using mathematical techniquescommon in information theory, such as by estimating the average codelength (i.e., the size of each compressed element) based on the entropyof the corresponding probability distributions of symbols. If thepredicted compressed data size after applying both the PartDict and theUniform filters is smaller than after applying the Uniform filter alone,then the PartDict filter may be applied.

An example of compression using PartDict is shown in FIG. 8. Input data802 contains elements with certain frequencies of occurrence. In thisexample, an element is considered to have a high frequency of occurrenceif it occurs at least twice. Thus, frequent values are 2, 3 and 5; rarevalues are 1, 4 and 6. The frequent values are placed in a dictionary804 with their associated frequencies of occurrence. Rare values 812 areassigned the symbol ESC. The frequent values are removed from the inputdata 802 and placed in a frequent value sequence 806 along with ESCwhere the rare values 812 should occur. The probability distributionstored in the dictionary 804 is used to encode the frequent valuesequence 806 using a standard coder 808, applying an encoding algorithm,such as RC or arithmetic coding (AC). The dictionary 804 and the codedfrequent value sequence are included in the filter stream 810. The rarevalues 812 form the filter output 814.

FIG. 9 shows the decompression of this example. The dictionary isdecompressed from the filter stream 810. The probability distributionfrom the dictionary 804 is used with the coder 808 to decode and recoverthe decoded frequent value sequence 816. The rare values 812 areprovided from the output of the previous filter in the decompressionprocess. The ESC symbols are replaced with the rare values 812 torecover the reconstructed input data 818. This example is for thepurpose of illustration only.

5. TopBitDict: builds a full dictionary of a group of the top bits ofall elements of the data. The dictionary also stores frequencies ofoccurrence of each group of top bits. The dictionary is compressed andsent to the filter stream. The top bits of each element of the data arecompressed by encoding with a standard compression algorithm such as RC,using the probability distribution represented by the dictionary. Theencoded sequence of top bits is sent to the filter stream. The lowerbits of the elements are left in the sequence, forming the filteroutput, and passed to the next stage. The number of top bits used forencoding may be chosen so as to minimize the predicted length of thecompressed data. Decompression is similar to that of PartDict.Evaluation of whether to apply the TopBitDict filter may be done usingpredicted compressed data size, similar to the technique for thePartDict filter.

6. LowBitDict: similar to TopBitDict, for the lowest bits of eachelement.

7. Outliers: considers the values of the elements rather than theirfrequencies of occurrence. First, outliers are detected in the datasequence by finding a small number of elements which have much larger ormuch smaller values than certain statistics (which may be dependent onthe data type) calculated for the rest of the elements. The thresholdfor determining what is considered an outlier may be pre-determined orit may be dynamically selected, depending on the data. In some cases,the number of values that may be considered outliers may bepre-determined. Then, the outliers are removed from the data sequence.The positions and values of the outliers are compressed and sent to thefilter stream. The data sequence with the outliers removed forms thefilter output. Reconstruction of the data may be done by decompressingthe values of the outliers and their position information and mergingthis information with the sequence of non-outliers.

Typically, determination of whether to apply the Outliers filter may bedone using predicted compressed data size, similar to the technique forthe PartDict filter. In some aspects, entropy calculations may beperformed to predict or to evaluate the optimum number of values thatmay be considered outliers.

8. Uniform: assumes uniform distribution of the data in the range of [0,max_value] and compresses all elements of the data by encoding with astandard encoding algorithm, such as RC. The filter output may betypically empty, and this filter typically may be the last filter in thefilter cascade for compression.

In addition to the data filters described above, there may also be amechanism for detection of repetitions of the same record. Beforecompression of the next record, it may be checked whether it is the sameas the previous record. If so, it may be given a certain encoding, e.g.,bit ‘1’. Otherwise, bit ‘0’ may be encoded. Use of this mechanism mayimprove the compression ratio and speed by 8%.

Compression filter stages containing the above-described data filtersmay be applied according to the ordering listed above, or the orderingmay be dynamically rearranged and some of the filter stages may berepeated based on the intermediate results of the output from precedingfilter stages. Before applying a filter stage a check may be optionallyperformed to determine if the filter stage should be applied. This checkuses the intermediate results to determine whether for efficiencypurposes the filter stage should be applied, (e.g., if the associatedrelationship/redundancy considered by the filter stage exists and if theapplication of the filter would result in compression gains sufficientto warrant the application of the filter, given the associatedperformance costs of compression and decompression).

Compression of NULL Masks and Binary Columns

The occurrences of 0 and 1 in the data column are counted to calculateprobabilities of 0 and 1 which are then passed to a standard compressionalgorithm, such as Arithmetic Coding (AC), which can yield a compressionratio of 2.6 to 1 on average. In some data packs there may becorrelations between neighbouring bits. To utilize this fact, thesequence may be differenced. Entropy, a well-known measure applied toprobabilities, may be calculated for the differenced sequence. If theentropy of the differenced sequence is lower than the entropy of theoriginal sequence, the differenced sequence may be encoded instead ofthe original sequence. This is similar to the Diff filter. Then, duringdecoding, the reverse operation may be performed. This modification maygive an average compression ratio of 3.5 to 1.

String Compression

Strings may be also compressed using a compression algorithm selectedfor the string data type. As in the case of numeric and binary data, thesequence of string values taken from a given data pack (e.g., for adatabase column keeping alphanumeric data) is first cleaned of NULLs andis then put through the filter cascade, as shown in FIG. 7A. The outputfrom each applied filter stage may take the form of a sequence of stringvalues. The general scheme of the filter cascade while compressing anddecompressing data may remain the same as before.

The list of available filter stages for sequences of string valuesremains open, as in the cases of other data types. An example of afilter which is applicable to string values is PartDict. The work ofPartDict, both during compression and decompression of a sequence ofstring values may be similar to the case of numeric values. In someaspects, strings may be compressed using an algorithm based onPrediction by Partial Matching (PPM), which is commonly used for textcompression. PPM is an adaptive technique. PPM attempts to predict thenext symbol in a string on the basis of the preceding symbols (i.e., thecontext). PPM is discussed in detail in, for example, “Data Compression”by David Salomon, 3rd edition, Springer-Verlag, 2004, Chapter 2.18; andin “Introduction to Data Compression” by Khalid Sayood, 3rd edition,Morgan-Kaufmann, 2005, Chapter 6.3, which are incorporated herein byreference in their entirety. PPM compresses strings using a probabilitydistribution for each symbol in a string based on the preceding symbols.This probability distribution may be updated continuously as moresymbols are encountered in the string. PPM may be used to compressstrings using a dictionary containing the probability distribution ofeach symbol.

Some implementations of PPM attempt to predict the next symbol using allpreceding symbols as the context, while other implementations use afixed length context. If this prediction is not possible, the contextmay be reduced by removing a preceding symbol from the context. This maybe repeated until a prediction can be made, or until there are no moresymbols in the context. When no symbols remain in the context, a fixedprediction may be made, which may be based on an assumption of uniformdistribution of all possible symbols. When a never-before seen symbol isencountered, an escape symbol may be used and a predeterminedprobability may be assigned to the new symbol.

Simply using PPM alone may be cumbersome because of the large amount ofmemory required to store the context, especially in the case where allpreceding symbols are used as the context. In such an implementation,searching for a given context in an encoded part of the string wouldgive O(n.sup.2) complexity. Instead, a trie or suffix tree datastructure may be used to represent the string. These data structuresstore the symbols in branching nodes in tree form. These data structurespermit faster context searching through the use of pointers back toearlier nodes, such pointers being known as suffix links. Use of a trieor a suffix tree for implementation of PPM is known in the art, andvariations are possible. The probability distribution for use in PPM mayalso be stored in the data structure, for example as weights on thenodes of a tree. This probability distribution may be continuouslyupdated as the symbols are processed.

PPM has been used to compress a large portion of text, such as an entiretext file. To use PPM for compression of a sequence of short strings,there are several approaches. PPM may be used to compress each stringseparately, however this method may not be able to exploit anysimilarity between strings. Another method may be to concatenate thestrings, perhaps separated by a special symbol, and compress the resultas a single string; however this method may not be able to exploitinformation about string boundaries to improve compression. Anothermethod may be to modify PPM to hold a sequence of strings, instead of asingle string. In this modification, the data structure may be modifiedto represent suffixes of all processed strings, and at the beginning ofcompression of the next string, the context may be reset to empty (e.g.,the root node of the tree in the case of a tree data structure).

In some aspects, a Compact Directed Acyclic Word Graph (CDAWG) datastructure may be used to implement PPM. A CDAWG is a data structure forstoring alphanumeric data in a way that permits fast word searching withdecreased space requirements. CDAWGs have been used for alphanumericdata storage. Aside from use in text searches, CDAWGs have also beenused in analysis of DNA sequences in bioinformatics.

Using a CDAWG as the data structure may provide the advantages that thedata is stored in a compact form, and that the space requirements areminimized. The data in a CDAWG may be stored in a compact form as in asuffix tree, where the edges (i.e., the path between adjacent nodes) ofthe tree may be labelled by more than one symbol, so that moving betweenadjacent nodes may give more than one symbol. The data tree in a CDAWGmay be minimized as in a Directed Acyclic Word Graph (DAWG), whereequivalent nodes of the tree are merged into a single node, thusavoiding redundant nodes.

Certain implementations of CDAWG may require input of all the symbolsbefore the CDAWG is created. This may be time-consuming and impracticalwhere there is a large amount of data. In other implementations, theCDAWG may be created on-line (e.g., in linear time, as the data is beingread) using the algorithm disclosed in Inenaga et al., “On-lineconstruction of compact directed acyclic word graphs”, Discrete AppliedMathematics 146(2):156-179, 2005, which is incorporated herein byreference in its entirety. By creating the CDAWG on-line, thealphanumeric data may be read through one symbol at a time, and theCDAWG may be created with suffix links, so that each subsequent symbolcan be efficiently added to the CDAWG.

The probability distribution of each symbol may be also calculated andupdated on-line as the data is being read, thus creating a CDAWG thathas the additional feature of weights for each node and/or edgecorresponding to the probability or frequency of that node and/or edge.As each symbol is read, the structure of the CDAWG may be modified asneeded, and the probability distribution of that symbol may be updated.When a never-before seen symbol is encountered, an escape symbol may beused and a predetermined probability may be assigned to the new symbol.In some aspects, when a new symbol is processed, the CDAWG may betraversed to see if a node for the symbol already exists. As the CDAWGis traversed, each edge and/or node that is passed may have its weightor probability distribution updated (e.g., by increasing the weight). Ifthe node that is needed does not exist, a new node and an associatedescape symbol may be created in the CDAWG, thus changing the structureof the CDAWG. In this way, a CDAWG containing probability distributionsfor each symbol is created on-line. The probability distributions canthen be used as a dictionary for compression using PPM.

In some aspects, the dictionary created by the data structure (e.g.,suffix tree or CDAWG) may be recalculated and reduced from time to time,to keep its size down where storage space is limited. This may be usinga process similar to the PartDict filter, where strings or symbols withlower frequencies may be pruned from the data structure.

PPM using CDAWG may offer an improvement over PPM using suffix treesespecially for compression of a sequence of short strings, as commonlyfound in databases. For such data, a CDAWG data structure may containover 10 times fewer nodes and 5 times fewer edges, the memory requiredmay be 7-10 times less, and the compression speed may be several timesfaster than if using a suffix tree.

In some aspects, more than one symbol may be encoded in one step of thecompression algorithm. This is possible because in CDAWG and in suffixtrees, an edge may be labelled by several symbols rather than a singlesymbol. In order to account for the situation where the string to beencoded matches only the beginning part of an edge label, the number ofsymbols matching the edge label may also be encoded in addition to thechoice of the edge. This number may have highly non-uniform and bi-modaldistribution, which can be exploited to improve the compression ratio.

Other possible implementation details include partial updates of datafrequencies, unbounded context length (e.g., where all preceding symbolsare used as the context), constant frequency for the escape symbol(e.g., the escape symbol may be given a probability assuming uniformdistribution), implementation of exclusions (e.g., correcting theprobability distribution of a symbol in a certain context to excludeoccurrences that are not possible), and use of a special start node inthe data tree. Such implementations and techniques are not discussedhere in detail, but are common in the art. Certain combinations of thesedetails may provide a better compression speed and/or ratio.

In some aspects, the data structure (e.g., suffix tree or CDAWG) may becreated when the data is first compressed, and may be discarded aftercompression. The data structure may be recreated every time the data isdecompressed.

Query Optimization

Query optimization refers to the optimal choice of functions andordering of functions to provide query results in the shortest amount oftime. Query optimization may improve resolution of query results bydecreasing the amount of data packs 221 that need to be retrieved from astorage medium, or that need to be accessed in memory. Queryoptimization involves simulating potential query execution plans todetermine the optimal query execution plan to answer a query. Queryoptimization does not actually access or retrieve data, rather it usesinformation about the number of data packs 221 that will need to beaccessed or retrieved to answer the query using DPNs 222 and KNs 224. Ifthe data itself is not accessed retrieved, the query plan executionsimulations may be substantially faster than the time required toactually execute the plan. Thus, query optimization in the RDBMS 200 maybe closely related to query execution. Query optimization may simulatenot only the overall candidate execution plans, but also the respectiveparts or execution steps to locate and avoid bottle necks that may occurin operations required to answer the query, for example delays that mayoccur as result of a non-optimal ordering of joining operations. Duringquery optimization, query execution steps may include first carrying outdatabase operations (e.g., sorting) using DPNs 222 and/or KNs 224 beforecarrying out the same operations on the individual data elements. Forexample, the data packs 221 may first be sorted using their respectiveDPNs 222 before sorting the data elements in each data pack 221.

Query optimization may be implemented by the Query Optimizer 208 (FIG.1), which may be a functional program module. The Query Optimizer 208may implement a heuristic program which begins with a candidate plan forthe query execution plan, simulates the result, then uses the simulatedresults to determine the amount of data that needs to be accessed orexecution time required by the candidate plan for the query executionplan. The Query Optimizer 208 may then attempt another candidate plan todetermine if performance may be improved.

Query optimization may use techniques such as rough set analysis (RSA)techniques to determine which data packs 221 need to be accessed byanalyzing the information of the DPNs 222 of each data pack 221 and theKNs 224 associated with each data pack 221. Query optimization may usethe concept of positive region, negative region, and boundary region fordealing with data packs that are fully relevant, fully irrelevant, andpartially relevant respectively. Data packs 221 determined to be in thepositive region (e.g., fully relevant) sometimes need decompression ifthe information cannot be obtained directed from the DPN 222 or KN 224,although typically not. Negative region (e.g., fully irrelevant) datapacks 221 do not need decompression. Boundary region (e.g., partiallyrelevant) data packs 221 usually need decompression, however there maybe some special cases where decompression is not required.

Query optimization may be carried out using rough queries as executionsteps. A rough query allows for query results to be approximated usingDPNs 222 and KNs 224. A rough query used as an execution step inoptimization may help minimize the amount of direct data access and/ordecompression needed by identifying which data packs 221 may containrelevant data.

Query optimization operations generally may include the following steps:

1. Choose a candidate plan for the query execution plan;

2. Simulate the data usage of the candidate plan at the level of datapacks 221 using only estimates from the KNs 224 and without accessing(i.e., decompressing) the underlying data;

3. Determining the potential usefulness of structures currently loadedin memory such as decompressed data packs 221, DPNs 222, and KNs 224;

4. Determining the data amount of data packs 221 that need to beaccessed (i.e., decoded or decompressed and loaded into memory),including possible cases when the same data packs 221 need to beaccessed multiple times, and/or an estimate of the amount of time toexecute the query; and

5. Repeating steps 1 to 4 for the next execution plan candidates untilthe shortest execution time may be determined or a satisfactoryexecution time may be determined. For example, a satisfactory executiontime threshold can be fixed based on historical or logged informationabout execution times of queries of comparable complexity or a timeout(e.g., this could occur after a fixed number of heuristic steps, or evenbefore reaching it if there is no improvement in the heuristic search).Alternatively, the minimum required memory can be optimized (see step 4above).

Each step in the above operation may be performed taking into accountthe result of the previous steps. If there is a series or conjunction ofconditions calculated in series, then records and data packs 221 whichare excluded earlier need not participate in the subsequentcalculations.

Information in the KNs 224 and/or the DPNs 222 may be used in such a wayas to optimize queries using minimal memory. In the case of a query thatinvolves sorting, sorting may be performed using encoded or “token”forms of the original values or, in the case of multi-column sorting,vectors of values. Such tokens are generated based on KNs 224 or DPNs222 of data packs that are relevant or partially relevant to a givenquery. Tokens replace the original values or the vectors of values whilesorting. Sorting over tokens provides the same results as if the datawere sorted by the original values or vectors of values. KNs 224 andDPNs 222 or irrelevant data packs are generally not used while producingthe optimal token forms.

In one non-limiting example, where the KN 224 is a CMAP object, asdescribed above, a query may be to sort strings. If the results of thequery all contain character ‘x’ in the second position, then the secondposition may be ignored when encoding strings into tokens, since thisposition has no effect on the results of the frequent query. Similarly,if the second position of the query result always contains only a subsetof characters (e.g., ‘x’, ‘y’, ‘z’ instead of the full alphabet), then asmaller code may be used to encode this position. Although this exampledescribes CMAP-based optimization of encoding for queries involvingsorting, similar encoding optimization may be carried out for other KNs224 and DPNs 222, for different data types. By optimizing encoding atthe level of the KNs 224 and the DPNs 222 rather than at the level ofdata elements, it may be possible to reduce memory requirements byexploiting relationships and redundancies that are not apparent at thelevel of individual data elements.

The DPNs 222 and the KNs 224 may be applied to sorting optimization alsoin the context of splitting data to be sorted into smaller portions thatmay be sorted separately and then merged in an optimal way. In this way,the DPNs 222 and the KNs 224 may help in further minimizing the size ofthe intermediate structures, such as those used for sorting in memory.For example, instead of minimizing the size of a single structure, thestructure may be split into smaller structures, which may be processedfaster separately, thus allowing for further memory optimization.

Information available in the DPNs 222 and the KNs 224 may influence thechoice of a method for accomplishing a given task, for example a joiningor sorting operation. If the method of accomplishing a task is alreadyselected, the way of using the DPNs 222 and the KNs 224 may differ inthe case of different methods. In one example, the DPNs 222 may be usedto detect two or more portions of data that may be sorted separately.Based on the DPNs, it may be found that the set of all data packs for agiven column may be split into subsets X and Y, where the minimum ofeach of the data packs belonging to X is greater than the maximum ofeach of the data packs belonging to Y. There may be more such subsetsthan just two. If subsets X and Y with such a strong property related tominima and maxima of the data packs are not found, then the data packsmay be split into several smaller subsets in such a way that the mergingof their elements sorted separately has a lower cost.

Query Optimization Example

The following example is for the purpose of illustration only. A partialexample of a query optimization procedure will now be described. First,assume that the query to be resolved involves a joining of threecolumns, A, B and C where A>7, B<6, and C=“YES”. During queryoptimization, the first candidate execution partial plan may first joinA and B and determine that this requires 1,000 data packs 221 to beaccessed to answer the subquery of A>7 and B<6. The Query Optimizer 208may then generate a second candidate execution partial plan where A andC are joined and determine that the subquery of A>7 and C=“YES” requires100 data packs 221 to be accessed. The Query Optimizer 208 may thengenerate a third candidate execution partial plan where B and C arejoined and determine that the subquery of B<6 and C=“YES” requires10,000 data packs 221 to be accessed. In this case, the Query Optimizer208 would choose to join the second candidate execution plan where A andB are joined, which minimizes the number of data packs 221 to beaccessed, and then join the result with C so as to minimize the numberof data packs 221 that need to be accessed to answer the query.

Hence, the query execution and optimization steps may interact withineach other unlike in conventional RDBMSs. The query simulation performedduring query optimization partially executes candidate execution plansand returns “intermediate results” to the Query Optimizer 208 forfurther analysis before continuing the execution of the query, andprovides the possibility of further execution plan modifications ifsubsequent candidate plans improve performance. Thus, unlikeconventional query optimization, the order in which subqueries areexecuted may be changed based on the intermediate results. Conventionalquery optimization and execution approaches typically use classicalindices, do not allow optimization during execution, and require data tobe accessed in the same order as in the query execution plan created bythe query optimizer.

KN information may be used for optimization and specifying the queryexecution plan, for example, using pack-to-pack joins to determine whichdata packs need to be joined during execution, although KN informationmay also be used before at the optimization level. During executionitself, after the optimal plan is determined, DPNs 222 and data packs221 may be utilized to answer the query. For example, the sums fromparticular data packs 221 may be obtained from DPNs 222 if the queryrequires the sum over some column.

Examples of Query Optimization Using Rough Queries

The following examples are for the purpose of illustration only. Anexample of rough query and iterative optimization for query optimizationwill now be described. Consider the following data packs in table T:

TABLE T Data Pack A1: Min = 3; Max = 25 Data Pack B1: Min = 10; Max = 30Data Pack A2: Min = 1; Max = 15 Data Pack B2: Min = 10; Max = 20 DataPack A3: Min = 18; Max = Data Pack B3: Min = 5; Max = 50 22

The minimum and maximum values of each data pack are stored in data packnodes associated with each data pack, and in this example the data packnodes are not compressed. The data pack nodes are included in theknowledge grid for this data set. Now consider the query SELECT MAX(A)FROM T WHERE B>15.

The first execution step is the following rough query:

ROUGH MAX(A) FROM T WHERE B>15

Using the knowledge grid, the results are that all data packs in columnB are relevant, and that MAX(A) is at least 18.

To determine whether the answer to the query may be greater than 18, thenext step is to identify the data packs that are still of interest inexceeding the value of 18. The following rough query is made:

ROUGH ID FROM T WHERE B>15 AND A>18

Here, ID is a command that returns the identity of the row of a datapack, together with the status of that data pack in terms of it beingrelevant or suspect for the specific query. Because MAX(A) is at least18, data packs A2 and B2 become irrelevant. Hence, this rough queryreturns the result: 1S; 3S, where S denotes that the data pack isrelevant or suspect.

Instead of using ID, the second step may be carried out using:

ROUGH A FROM T WHERE B>15 AND A>18

Where the result not only identifies relevant rows, but also estimationsof the values of A in each row: 1S [3,25]; 3S [18,22].

The above information may be used to estimate within which data pack theactual maximum is most likely stored. This may be carried out using aheuristic algorithm (e.g., greedy, randomized, evolutionary, or otherAl-based algorithms). Such an algorithm may be employed by the commandORDER BY, which orders the rows by increasing likelihood of relevance.So the following rough query may be used instead as the second step:

ROUGH ID, A FROM T WHERE B>15 AND A>18 ORDER BY A

In this example, assume that the heuristic algorithm finds data pack A1to more likely have a MAX greater than A3. So the result is: 3S [18,22];15 [3,25].

Now the data in data packs A1 and B1 are directly accessed, which mayinclude decompressing the data packs. Taking advantage of the results ofthe ORDER BY execution step, the data packs A1 and B1 are accessedfirst. The next query execution step is:

SELECT MAX(A) FROM T WHERE B>15 AND ID=3

Assume the result of this query results in the value 23. Rather thandirectly accessing the data of data packs A3 and B3 with a similarquery, a rough query may be carried out to iteratively optimize thequery. Thus, the next execution step is:

ROUGH ID FROM T WHERE B>15 AND A>23

Using the knowledge grid, the data pack node for A3 shows that A3 doesnot contain any values greater than 23. Thus, the final result of thequery is 23.

Thus the query optimization using rough queries may prune the data packsof interest and may further identify where relevant results are mostlikely to be found, in order to reduce the amount of direct data accessand data decompression used. Rather than having to directly access eachdata pack, the data pack nodes and/or knowledge nodes included in theknowledge grid may allow a query to be optimized using statisticaland/or analytical information.

Now, consider the same table T which was used above, and the followingquery:

SELECT A, MAX(B) FROM T GROUP BY A

Assume that the query execution steps include creating a memory bufferof limited size (e.g., 5 positions) and storing the grouping values ofA, as well as the maximum value B for each group. The initial executionplan may be to scan the column A to gain up to 5 distinct values and tofind the appropriate maximum values of B in each group. Next, the buffercontent is stored as a part of the query result and the whole table T issearched again for values of A that were not stored in the bufferpreviously. These passes are repeated until all values in column A aregrouped.

Using the knowledge grid, the optimal order of the data packs may bedetermined by the rough query:

ROUGH ID FROM T ORDER BY MAX(A)-MIN(A)

(e.g., scanning begins from the data pack having the minimal spanbetween the maximal and minimal value of A. The result is 3S, 2S, 1S.)

The grouping buffer is filled by values of pack A3. Since all 5positions are already occupied, the pack A2 becomes locally irrelevantsince it cannot contain any value [18,22] on column A. Now, assume thatthe maximal values of B for these groups are all larger than 40. In thiscase, B1 also becomes irrelevant since it cannot change the alreadyknown maximal values for these groups.

The first pass of the algorithm is concluded and all records havingvalue A in [18,22], including the whole pack A3, become irrelevant forall further passes. At least four data pack accesses (A2, A1, B2, B1)were avoided compared to the situation where the knowledge grid is notused.

In cases where the knowledge grid indicates that all rows in a packrowcontain single values over each of the columns used for grouping, thestatistics on aggregated columns stored in the knowledge grid (e.g.,minimum, maximum, and sum) may be directly used to populate the resultof grouping.

Join Query Optimization Using Knowledge Nodes Example

The following example is for the purpose of illustration only. Anexample of a join query and optimization of the query execution usingJPP, HIST, CMAP and other information stored in the DPNs will now bedescribed. Consider the tables T1 and T2, consisting of 10 and 8 datapacks, respectively, and the query:

SELECT T1.A FROM T1 JOIN T2 ON T1.K=T2.K WHERE T2.B>10

Assume that using the knowledge grid, data packs B1, B2 and B3 may beidentified as irrelevant. Assume also that JPP Knowledge Node exists forT1 and T2 and its contents is as follows:

T1 T2 1 1 1 0 0 0 1 1 0 0 0 1 1 1 0 0 0 1 0 0 0 0 0 0 0 0 1 1 0 0 0 1 00 0 1 1 1 0 0 0 0 0 1 1 1 1 0 0 0 1 1 1 1 1 1 0 0 0 0 1 0 0 0 0 1 1 1 00 1 0 0 0 0 0 0 0 1 1

If there is no JPP Knowledge Node calculated, its contents may beapproximated based on data pack nodes (e.g., minimal/maximal values ofthe pairs of packs), HIST or CMAP. The way of initializing the local JPPnode for a query is not needed for the example.

Given the information that B1, B2 and B3 are irrelevant, the modifiedJPP node for the query is prepared as follows:

T1 T2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 00 0 1 1 1 0 0 0 0 0 1 1 1 1 0 0 0 1 1 1 1 1 1 0 0 0 0 1 0 0 0 0 1 1 1 00 1 0 0 0 0 0 0 0 1 1

To determine the final answer, a joining of columns T1.K and T2.K isperformed. Assume that the general execution plan for joins includesseveral steps, each of the steps including selection of subsets ofpotentially joinable rows from T1 and T2, ordering them by values ofjoining columns, and then matching column values to enlist a part of asolution. The decomposition of the joining process into steps may bedone in such a way that every pair of rows matched in one step may notbe matched in any further step.

Assume that a cost function value may be determined for all steps. Thecost may be dependent on the size of ordered data, the number of openeddata packs, and other factors influencing the total execution time andwhich are determinable based on the JPP node.

A heuristic, incremental decomposition of a joining operation into stepsmay be performed to minimize the total cost. Assume that the cost ofjoining n data packs with m other data packs is (n log n+m logm+16(n+m)) and that any sorter for which n+m>10 will not fit in memoryand an additional penalty is added in this case. For the example above,a heuristic optimizer may propose the following steps:

join packs 1, 2 from T1 with 4, 5, 6 from T2;

join packs 1, 9, 10 from T1 with 8 from T2;

join packs 3, 4, 5, 6, 7 from T1 with 5, 6 from T2; and

join packs 6, 7, 8 from T1 with 4, 7 from T2. This execution plan ishighlighted in the table below:

This query optimization using rough information stored in the local JPPnode, gathering information from both one-dimensional dependencies andjoin conditions, may minimize the total cost of joining tables. The costmay be minimized in all aspects, including the number of data packsaccessed, the number of times the accessed data packs are accessed, thesize and speed of intermediate structures, and known memory constraintsmay be addressed.

Data Structure

FIG. 10 illustrates the organization of the data within the RDBMS 200.In system 1000, data storage may be separated into three primarycomponents: a database 1004 which may be either distributed orpartitioned as shown or undistributed, a local cache 1002 databaseengine invocation (e.g., a temporary cache folder on the local HDD), anda central repository 1006. All of these data stores 1002, 1004, and 1006may be located on different devices, however the database 1004 andcentral repository 1006 are sometimes stored together. Every KN 224 maybe represented by one file in the central repository 1006.

In some aspects, the type and parameters of each KN 224 may be encodedin its file name. In some aspects, each KN 224 may have a file name withthe format:

XXXXXX.a.b.c.d.e.f.g.h.rsi

where XXXXXX is a 6-character type identifier; a, b, c, d, e, f, g, hare numbers which identify objects and optionally may be type-dependent;and .rsi identifies the file as a KN 224. The repository 1006 maycontain a definition file that describes its contents, and thedefinition file may have a file name of the format: rsi_dir.rsd.

The central repository 1006 may use periodic maintenance to check forthe following possible inconsistencies:

i.) whether there is a .rsi file not described in a repositorydefinition file. If so, the .rsi file should be removed;

ii.) whether the definition file contains entries which do not match anyexisting file. If so, these entries should be removed or a matching fileshould be scheduled for creation;

iii.) whether the KNs 224 are locked (e.g., marked as “working” asdescribed below), but no client application 202 is using these KNs 224.If so, these KNs 224 should be unlocked.

The central repository 1006 may be accessible by multiple clientapplications 202 (FIG. 1) (e.g., multiple users) simultaneously, so thecentral repository 1006 may allow parallel access. To accommodatemultiple client applications 202, a file locking mechanism may be used.KNs 224 may be typically small and may be loaded entirely to memory inwhich case access to them may be exclusive, for example only one processmay read or write a particular KN 224 at one time. A method forresolving sharing conflicts may be as follows:

i.) when writing to a KN 224 is in progress, then if a first processtries to open a KN 224 that is being written by a second process, accessof the first process to the KN 224 may be denied;

ii.) when a KN 224 increases in scope so old data is still valid, thenif the information stored by the Knowledge Grid Manager 220 is obsolete(e.g., because the data pack(s) on which the KN 224 is based has beenincreased in scope), the KN 224 may be loaded into memory and the KN 224may be updated after being loaded;

iii.) when a KN 224 decreases in scope so old data may not be valid,then if the information stored by the Knowledge Grid Manager 220 isobsolete (e.g., because the data pack 221 on which the KN 224 is basedhas been decreased in scope), the KN 224 may be not loaded into memoryand the operations may proceed without the KN 224 by proceeding toaccess the DPN 222 or data in the respective data pack 221;

iv.) when a new KN 224 is created which supplements but does not replacean old KN 224, then if the information stored by the Knowledge GridManager 220 is obsolete (e.g., because there is a new KN 224), the olderKN 224 may be loaded into memory.

Unlike conventional database indices which cannot be accessed once theunderlying data has changed unless the database indices have themselvesbeen updated (typically, data and database indices must be updatedtogether, absent which the data cannot be accessed), data packs 221 andDPNs 222 in the RDBMS 200 may be updated independently of any associatedKNs 224. This allows updated data in data packs 221 to be accessedalmost immediately whereas associated KNs 224 may not be availabledepending on whether such objects have been updated. This may beadvantageous in that some KNs 224 may be time consuming to update.Therefore, KNs 224 which are quicker to update may be updated shortlyafter the underlying data pack 221, while more time consuming KNs 224may be updated at a convenient time such as when the RDBMS 200 is lessactive.

When a new Knowledge Grid Manager 220 task is scheduled, it may bewritten in a repository definition file. To write to the definitionfile, the file may be reopened in exclusive mode, after which it may beread. If the file does not yet contain the desired task, the task may beadded and the file may be written to disk and then unlocked. A similarprocedure may be used when an Knowledge Grid Manager 220 decides toexecute a scheduled task: first, the definition file may be openedexclusively to read the task definition, assuming it is still available,then the task may be marked as “working”, the file may be written backto disk and unlocked. Meanwhile, the KN object file may be locked andits recalculation may start. After a successful object update, therepository definition file may again be locked and updated as the taskis removed from the schedule.

Tiered Data Warehouse Architecture

FIG. 11 illustrates a tiered data warehouse system 1100. The system 1100may include a primary warehouse 1102 coupled to an archival secondarywarehouse 1112 via a Tiered Warehouse Management Console 1107. Betweenthe primary warehouse 1102 and the secondary warehouse 1112 are theimport/export modules, collectively 1103, that facilitate data transferbetween the primary warehouse 1102 and the secondary warehouse 1112. Theprimary warehouse 1102 may include a RDBMS 200 coupled to a databaseincluding one or more base tables each including one or more datarecords. The RDBMS 200 may include a conventional RDBMS, for examplesuch as that provided by MySQL.RTM., Teradata.TM., Oracle.RTM., etc.

One or more users may access the primary warehouse 1102 via respectiveclient applications 1104 implemented on user terminals (not shown). ASQL-based interface may be provided by the client application 1104 toaccess the primary warehouse 1102 using standard SQL query tools. Thesystem 1100 may optionally include one or more of a listener 1106 whichmonitors and stores queries (e.g., SELECT statements) or alternativelyqueries are extracted from the primary warehouse 1102 and/or secondarywarehouse 1112 log files. The resulting query log 1118 may be providedto the Query Log Analyzer 1120.

Users accessing the secondary warehouse 1112 may use the same clientapplication 1104. A SQL-based interface may be provided by the clientapplication 1104 to access the secondary warehouse 1112 using standardSQL query tools. Unlike the primary warehouse 1102, the secondarywarehouse 1112 may include an RDBMS 200 coupled to a database includingone or more data packs 221 each associated with basic analyticalinformation in the form of DPNs 222 and having associated therewithadvanced analytical information in the form of KNs 224.

The Query Log Analyzer 1120 may be a functional program that analyzesquery information collected in the raw query logs 1118 to determine theusage of data by users over a given time period within the primarywarehouse 1102 and secondary warehouse 1112. The Query Log Analyzer 1120analyzes the frequency of data usage or access within the primarywarehouse 1102 and secondary warehouse 1112. Alternatively, specificquery statistics need not be recorded. Instead of maintaining specificstatistics, an approximation of the queries on specified data (e.g.,data ranges based on time or some other factor) using a satisfactorystatistical sampling of queries may yield faster results and stillprovide the desired information on user queries. The Query Log Analyzer1120 may also identify the user who executed the query, the tables andcolumns and the manner in which the columns where referenced within thequery (e.g., part of the result set, used in JOIN criteria or used forfiltering or sorting results).

The Query Explorer 1109 allows a user to examine the results of theQuery Log Analyzer 1120 that are stored in the query usage statistics1108. Depending on the frequency of data usage and pre-determinedperformance criteria, the Query Explorer Module 1109 may identifycandidate data in the primary warehouse 1102 that may be moved to thesecondary warehouse 1112 to improve the performance of the system 1100.The Tiered Warehouse Management Console 1107 may manage the tieredenvironment, may identify candidate data via the Query Explorer 1109 inthe primary warehouse 1102 that may be moved to the secondary warehouse1112, and may manage the data movement (e.g., data migration) process,using the service of the Job Management Service 1105, which coordinatesthe activities of standard import and export utilities 1103 provided bythe primary and secondary warehouse 1102 and 1112. The Job ManagementService 1105 uses encoding operations similar to the operations 300described above when exporting data from the primary warehouse 1102 tothe secondary warehouse 1112. Base tables from the primary warehouse1102 are encoded and stored in data packs 221 with DPNs 222 and KNs 224in the secondary warehouse 1112.

The performance criteria of the Tiered Warehouse Management Console 1107may specify an access frequency (e.g., in terms of % usage in a giventime interval), relative access rate of selected data, or otherpre-defined conditions which identify data in the primary warehouse 1102as being suitable for transfer and archiving in the secondary warehouse1112. The user may be allowed to specify parameters, for example theuser may specify that only 10% of data should be left in the primarywarehouse 1102. In this case, the Tiered Warehouse Management Console1107 may search for the 10% or some other suitable percentage which ismost frequently used in queries to keep in the primary warehouse 1102and migrate the remainder to the secondary warehouse 1112, and mayexpress the most active 10% using predefined time dimension ranges.Typically, data access in the primary warehouse 1102 may be mostfrequent with new or freshly added data. As data ages in the primarywarehouse 1102, it typically becomes accessed less and less. Inaccordance with some implementations, once data access falls below somepredetermined threshold, for example if a base table is accessed lessthan once a month, the data may be selected for archiving, (e.g., to betransferred to the secondary warehouse 1112). The conditions andthresholds for archiving are variable and may be set to optimize theperformance of the overall system 1100.

The Query Log Analyzer 1120 also analyzes the usage of data in thesecondary warehouse 1112 using information collected by its respectivelistener and/or query log. If data in the secondary warehouse 1112 isbeing accessed more frequently, it can be exported back from thesecondary warehouse 1112 back to the primary warehouse 1102 to satisfythe relevant performance criteria of the system 1100. The Job ManagementService 1105 may use decoding operations similar to those describedabove when exporting data from the secondary warehouse 1112 to theprimary warehouse 1102. Data packs 221 from the secondary warehouse 1112are decoded to the underlying base tables to be exported back to theprimary warehouse 1102.

The Query usage statistics 1108 may also be used to optimize the KNs 224in the database of the secondary warehouse 1112 so as to find theoptimal KNs 224 based on query patterns, using an approach similar tothe Knowledge Grid Optimizer 240 described above. The Query ExplorerModule 1109 may also be used to analyze queries to understand how usersare relating data thereby enabling further analysis of the data. Usingthis query information, reports or other information about data usagemay be used to better organize or use the data. In addition to analyzingquery data usage with respect to time dimensions, query data usage mayalso be estimated with respect to other dimensions. For example, it maybe possible to detect that user group x runs queries over a much widertime ranges than user group y, or that column A is the only columnaccessed in table T by user group z. Similarly, the Query Explorer 1109may also indicate how column data is referenced in queries, enablingbetter tuning of the primary warehouse 1102. For instance if tables arefrequently joined, perhaps the tables should be combined. If a column isfrequently used for filtering, an index may improve query speed.

The tiered architecture of the system 1100 seeks to address the problemthat many data warehouses operate at or near capacity, and that theamount and detail of data being stored is ever increasing. While addingstorage capability may accommodate increasing amounts of data, it may becostly and may not improve system performance or responsiveness. Inaddition, many database management systems have limits on how much datacan be handled and managed to ensure performance levels are reasonable.The tiered architecture of the system 1100 allows less frequently useddata, typically older data, to be removed from the primary warehouse1102 to a secondary warehouse 1112 where the data can be compressed toreduce storage requirements. The system 1100 also allows for therestoration of data from the secondary warehouse 1112 to the primarywarehouse 1102, if desired. However, in many cases, users simply do nothave the disk space to accommodate large data set restores, presentinganother advantage of the tiered warehouse approach.

In accordance with the tiered architecture of the system 1100, theprimary warehouse 1102 may be implemented using an RDBMS 200 suitablefor handling larger volumes of users efficiently, and configured tosupport a large number of reports that are executed regularly (e.g.,daily, weekly, monthly, or annually). In this way, the primary warehouse1102 may be used to reference more current data, for example such as thelast two years. The secondary warehouse 1112 may be used to referenceolder data, which typically supports more analytical functions such aswhere long term histories are required to determine trends orstatistical analyses. In this way, the types of queries performed on theprimary warehouse 1102 and secondary warehouse 1112 may differ in termsof database tuning.

However, the tiered architecture allows short-term functions (e.g.,reporting) to be implemented on the primary warehouse 1102 whileanalytical functions may be implemented on the secondary warehouse 1112.This may simplify data access and may allow improved tuning of theentire system 1100, which may improve overall performance and loweringcosts by implementing a portion of the system 1100 on a lower cost,secondary warehouse 1112 of compressed data. In addition, using KNs 224representations in the secondary warehouse 1112 where analytical typequeries are more common may provide synergies in that the answer toanalytical queries may be found directly in the DPNs 222 and KNs 224themselves, negating the need to access the underlying data. Manystatistical results are pre-calculated and stored with the DPNs 222and/or KNs 224.

FIG. 12 illustrates a tiered data warehouse system 1200. The system 1200is similar to the system 1100 in that it may include a primary warehouse1206 for “current data” or more frequently accessed data, and asecondary warehouse 1208 for “older data” or less frequently accesseddata which may be compressed in data packs 221 and which contains DPNs222 and KNs 224. However, the system 1200 also incorporates a SeamlessQuery Module 1204 and Unified Knowledge Grid 1210 for the primarywarehouse 1206 and the secondary warehouse 1208. The Seamless QueryModule 1204 includes information about the data of the primary warehouse1206 and the secondary warehouse 1208 to provide a seamless queryinterface to a user via a client application 1202. A user using theclient application 1202 may be provided with a SQL query interface toperform queries without regard to whether the data referenced by thequery is stored in the primary warehouse 1206 and/or secondary warehouse1208. The underlying tiered architecture of the system 1200 may beinvisible to the user. Depending on the query parameters, the SeamlessQuery Module 1204 may determine whether the referenced data is in theprimary warehouse 1206, secondary warehouse 1208, or in rare cases both.The Seamless Query Module 1204 then engages the respective databaseengine of the primary warehouse 1206 and/or second warehouse 1208, asnecessary.

Although the methods and systems of the present disclosure are describedin the context of data packs 221 and DPNs 222, the concept of aknowledge grid using statistical data elements may be created also forother database systems based on a conventional database having basetables and conventional database indices without the need of storingdata packs 221 and DPNs 222.

FIG. 13 illustrates a computing device architecture 1300 that may beused with the systems described. The computing device architecture 1300may be representative of the client application 202, or any of thecomputing devices, servers, or computers described above. The computingdevice 1300 generally may include a bus 1301, a one or more than onemicroprocessor or processor 1302, a memory 1304, a display 1306, one ormore user input devices 1308, and a communication interface 1309, whichmay all be coupled to the bus 1301. The computing device 1300 mayadditionally include a display device (not shown) for communicating anoutput to a user. In one example, the user input devices 1308 may be akeyboard or pointing device such as a mouse. The communication interface1309 provides an interface for communicating with a network 1326. Anoperating system 1310 or applications 1312 run on the processor 1302.The memory 1304 includes Random Access Memory (RAM) 1316, Read OnlyMemory (ROM) 1318, and a disk 1320. In one example, the data processingsystem 1300 may include either a client or a server. Any of the softwaremodules or components mentioned above may be stored in the memory 1304for execution by the processor 1302.

In accordance with some aspects, there is provided a method for encodingcolumn data from a base table in one or more data packs, the base tableincluding one or more columns of data, the method including: selecting acolumn in the base table; selecting a compression algorithm based on adata type in the column; compressing data from the column using theselected compression algorithm; and storing the compressed column datain a data pack.

In some aspects, each of the two or more columns of data may be at leastone of: a string, a numeric value, floating point value, and binary.

In some aspects, the method may further include, before compressing thecolumn data: generating a null mask representing the positions of nulland non-null value positions in the column; generating a reduced dataset from the column data, including removing the null position from thecolumn data; and wherein the data from the column compressed in thecompression step is the reduced data set, and wherein the null mask isstored in the data pack the compressed column data.

In some aspects, the data warehouse system may include more than onecomputing device accessing data, the KNs 224 and the DPNs 222 stored ina common disk space. In one example, one computing device may processone user query and may access the KNs 224 updated by another computingdevice.

These and other aspects and features of the present disclosure willbecome apparent to persons of ordinary skill in the art upon review ofthe above detailed description, taken in combination with the appendeddrawings.

While the present disclosure is primarily described as a method, aperson of ordinary skill in the art will understand that the presentdisclosure is also directed to an apparatus or system for carrying outthe disclosed method and including apparatus parts for performing eachdescribed method step, be it by way of hardware components, a computerprogrammed by appropriate software to enable the practice of thedisclosed method, by any combination of the two, or in any other manner.Moreover, an article of manufacture for use with the apparatus, such asa pre-recorded storage device or other similar computer readable mediumincluding program instructions recorded thereon, or a computer datasignal carrying computer readable program instructions, may direct anapparatus to facilitate the practice of the disclosed method. It isunderstood that such apparatus, articles of manufacture, and computerdata signals also come within the scope of the present disclosure.

The embodiments of the present disclosure described above are intendedto be examples only, for the purposes of illustration and not intendedto be limiting. Those of skill in the art may effect alterations,modifications and variations to the particular embodiments withoutdeparting from the scope of the present disclosure. In particular,selected features from one or more of the above-described embodimentsmay be combined to create alternative embodiments not explicitlydescribed, features suitable for such combinations being readilyapparent to persons skilled in the art. The subject matter describedherein in the recited claims intends to cover and embrace all suitablechanges in technology.

1. A method of resolving a data query in a data processing system,wherein the data in the data processing system includes a plurality ofindividual data elements arranged in a table having columns and rows,each of the data elements corresponding to a row and column, the tablegrouped into a plurality of row units, wherein each row unit comprises aplurality of rows of the table, wherein each row unit comprises adifferent set of rows, and wherein a first data unit corresponds to afirst row unit and a first column, wherein the first data unit comprisesa plurality of data elements, the plurality of data elements gathered inthe first data unit, the first data unit corresponding to a firstinformation unit, the first information unit gathering a first set ofinformation about the first data unit, the first set of informationcomprising a representation of information that is less than all uniqueinformation in the first data unit, comprising: executing an executionplan, wherein the execution plan includes a sequence of data processingoperations; using the information in at least the first information unitto identify data units whose data elements are sufficient to complete afirst data processing operation; retrieving a first set of data elementssufficient to complete the first data processing operation; completingthe first data processing operation using the first set of informationand the first set of data elements; and if the first data processingoperation is the last operation in resolving the data query, returningthe results of the data query for use by the data processing system andif the first data processing operation is not the last operation inresolving the data query, returning intermediate results of the firstdata processing operation for use by the remaining data processingoperations in the execution plan.
 2. The method according to claim 1,wherein the rows are organized into the row units according to anordering of the rows corresponding to the order in which the rows havebeen received by the data processing system.
 3. The method according toclaim 1, wherein the rows are organized into the row units in such a waythat the data elements in the first data unit are described moreaccurately by the first information unit than the data elements in asecond data unit are described by a respective second information unit.4. The method according to claim 1, wherein the data elements gatheredwithin the data units are physically stored by the data processingsystem or by an independent storage system.
 5. The method according toclaim 1, wherein the data elements of the first data unit are calculatedusing a calculation plan, the calculation plan being stored in the firstinformation unit, the calculation plan being used only when the firstdata unit is to be retrieved.
 6. The method according to claim 1,wherein the first information unit stores information about more thanone data unit.
 7. The method according to claim 1, wherein the firstinformation unit stores information about at least one of: a value, asubset of values, or a range of values which do not occur in the firstdata unit; a value, a subset of values, or a range of values which occurin the first data unit.
 8. The method according to claim 1, wherein thefirst information unit stores information about an occurrence of valuesthat are not typical for the first data unit or multiple data units. 9.The method according to claim 1, wherein the first information unitstores information about at least one of: the number of data elements inthe first data unit, the number of non-NULL data elements in the firstdata unit, the number of NULL data elements in the first data unit, thenumber of distinct values of data elements in the first data unit, theminimum value out of data elements in the first data unit, the maximumvalue out of data elements in the first data unit, the total value ofnumeric data elements in the first data unit, and the total length ofalphanumeric data elements in the first data unit.
 10. The methodaccording to claim 1, wherein the size of the first information unit isat least an order of magnitude smaller than the size of the first dataunit.
 11. The method according to claim 1, wherein completing the firstdata processing operation further comprises: retrieving the dataelements in a subset of the data units whose data elements are requiredto be retrieved to complete the first data processing operation toobtain a partial resolution to the first data processing operation; andusing the partial resolution and the first set of information in thefirst information unit to re-identify whether the first data unit maycontain the data elements that are needed to be retrieved in order tocomplete the operation.
 12. The method according to claim 11, whereinthe first information unit is used to select one or many data unitswhose data elements, after their retrieval, result in the most completepartial resolution.
 13. The method according to claim 11, wherein theamount of selected data units is at least an order of magnitude lowerthan the amount of all available data units.
 14. The method according toclaim 11, wherein retrieving the data elements in at least a portion ofthe data units and re-identifying any further data units are repeateduntil the partial resolution is sufficient to complete the operation.15. The method according to claim 1, wherein the first information unitis used to categorize the first data unit into categories including:first data unit that has no data elements relevant for further resolvingthe data processing operation; first data unit where all of the dataelements in the first data unit are relevant for further resolving thedata processing operation; and first data unit where the information inthe respective information unit is insufficient to categorize the firstdata unit.
 16. The method according to claim 15, wherein, if all thedata elements in the first data unit are relevant for further resolvingthe data processing operation, the first set of information stored inthe first information unit is used in resolving the data processingoperation without retrieving any data elements from the first data unit.17. The method according to claim 15, further comprising: retrievingdata elements of a second data unit, wherein information in therespective information units is insufficient to categorize the firstdata unit and the second data unit; obtaining a partial resolution tothe data processing operation based on the first information unit andthe data elements of the second data unit; and using the partialresolution and the first set of information in the first informationunit to re-categorize the first data unit.
 18. The method according toclaim 1, wherein the first set of information in the first informationunit is used to perform a data processing operation on the first dataunit and the intermediate results of previous data processing operationsin the execution plan.
 19. The method according to claim 1, wherein thefirst set of information in the first information unit is dynamicallyaltered by a result of at least one of the data processing operations inthe execution plan.
 20. The method according to claim 1, furthercomprising a second information unit, wherein the second informationunit is dynamically created while resolving a data query, the secondinformation unit gathering a second set of information about a subset ofdata elements of an intermediate result of previous data processingoperations in the execution plan.
 21. The method according to claim 1,wherein at least one data processing operation is defined using alanguage of information queries, wherein the information queries areresolved based on information units, with limited access to the dataunits.
 22. The method according to claim 21, wherein the informationqueries are resolved with no access to the data units.
 23. The methodaccording to claim 21, wherein the information queries are resolved byaccessing an amount of data units which is at least an order ofmagnitude lower than the amount of all available data units.
 24. Themethod according to claim 21, wherein the results of the informationqueries resolved in the data processing operation are stored in a formof new information units which can be used while resolving further dataprocessing operations in the execution plan.
 25. The method according toclaim 1, wherein the data query is selected from the group consisting ofa SQL statement, a statement of SQL language extension and an operationreceived by a programmable interface.
 26. The method of claim 1, whereinthe size of the first information unit is not greater than 1% of thesize of the first data unit.
 27. The method of claim 1, wherein eachplurality of row units comprises disjoined sets of rows.
 28. The methodof claim 1, wherein when the data in the data processing system isarranged in the table it is grouped into row units of a size of 2¹⁶rows.
 29. The method of claim 1, wherein when the data in the dataprocessing system is arranged in the table it is grouped into row unitssuch that the number of rows in a first row unit is different than thenumber of rows in a second row unit.